Live-Forum - Die aktuellen Beiträge
Datum
Titel
17.04.2024 21:55:37
Preiserhöhung über mehrere Jahre berechnen
17.04.2024 20:19:32
Endzeitpunkt berechnen aus d:hh:mm errechnen
17.04.2024 18:57:33
Errinnerungsfunktion
17.04.2024 16:56:58
Stunden addieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1496to1500
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1496to1500
1496to1500
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Adressübernahme u. Anrede via ComboBox

Adressübernahme u. Anrede via ComboBox
17.06.2016 09:41:25
Lukas
Hallo Leute,
ich kämpfe seit zwei Tagen mit folgendem Problem:
Ich möchte eine ComboBox in "Sheet A" (=Vorlage) mit Adressen (genauer gesagt Nachnamen) aus "Sheet B" (=DB) befüllen.
Sobald in der CBox ein Nachname angeklickt wird soll eine Zelle darunter die vollständige Adresse (in vier fortlaufenden Zeilen) automatisch eingetragen werden.
Weiters soll auch die Anrede, welche via WENN-Formel in "Sheet B" ermittelt wird, in die Vorlage übernommen werden.
Ansätze wie dieser führten leider nicht zum gewünschten Erfolg: 
Private Sub Worksheet_Activate()
With Worksheets("MaterialVerw-DB")
ComboBox1.List = Range("Institution").Value   '=Spalte Institution
ComboBox1.List = Range("Name").Value   '=Spalte Privatperson
End With
With ws.Range(ws.Cells(StartZeile, StartSpalte), ws.Cells(EndZeile, EndSpalte)).Validation
.Delete
.Add _
Type:=xlValidateList, _
Formula1:="=NameDerDropDownListe"
ComboBox1.List.Value = "('MaterialVerw-DB'!L5)"   '=Anrede Spalte L
End With
End Sub

Zur deutlicheren Veranschaulichung habe ich das aktuelle File hochgeladen.
https://www.herber.de/bbs/user/106299.zip
Wäre echt toll wenn ihr mir weiterhelfen könntet.
Ich mache das unentgeltlich für eine gemeinnützige Organisation.
Gruß
Lukas

21
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Adressübernahme u. Anrede via ComboBox
17.06.2016 10:01:31
Daniel
Hi
ich würde das so machen:
verlinke die Combobox auf der Vorlage mit dem Zellbereich in der Datenbank.
Schreibe hier zu in die Eigenschaft ListFillRange der Combobox die Zelladresse der Tabelle soweit sie angezeigt werden soll: ='MaterialVerw-DB'!A2:J6
setze die Eigenschaft ColumnHeads auf True
Schreibe in die Eigenschaft ColumnCount die Anzahl der Spalten (10) und gib in der Eigenschaft ColumnWidth die Breite der Spalten an, so wie sie in der Klappliste angegzeigt werden sollen, für jede Spalte mit Semikolon getrennt. (0 = Spalte nicht anzeigen)
setze die Eigenschaft BoundColumn der Combobox auf 0
gib in der Eingenschaft LinkedCell die Zelladresse für eine Ausgabezelle an z.B.: A1
in diese Zelle wird dann die Indexnummer des gewählten Eintrags geschrieben, der erste Eintrag hat den Index 0
in den Zellen für Anrede, Adresse, Kundennummer usw kannst du dann mit folgender Formel den entsprechenden Wert aus der Datenbank eintragen lassen (Spalte entsprechend anpassen)
=Index('MaterialVerw-DB'!L:L;$A$1)
die Ausgabezelle (A1) kannst du ja hinter dem Logo verstecken.
du siehst, das ganze funktioniert ohne Makro, nur mit Formeln und mit den richtigen Einstellungen für die Combobox.
Gruß Daniel

Anzeige
AW: Adressübernahme u. Anrede via ComboBox
17.06.2016 13:04:14
Lukas
Hallo Daniel,
DANKE für deinen Ansatz. Ich werde mir die beschriebene "ohne Makro" Variante heute Abend ansehen.
Gruß
Lukas

AW: Adressübernahme u. Anrede via ComboBox
17.06.2016 13:15:07
Lukas
Hallo Peter,
DANKE für deine Lösung.
Ich hätte nicht gedacht, dass nur so wenige Codezeilen benötigt werden.
Ich verstehe das Coding "so in etwa".
Kannst du mir die paar Zeilen trotzdem kurz erläutern damit ich mir die Kundennr. selbst hinzufügen kann und eine Art Wissenskontrolle habe?
Noch etwas: Wenn die Zelle "Name" leer ist (weil es sich um eine Institution handelt), wie kann ich es machen, dass in der ComboBox die Bezeichnung der Institution angezeigt wird?
Gruß
Lukas

Anzeige
AW: Adressübernahme u. Anrede via ComboBox
17.06.2016 13:41:25
Peter
Hallo Lukas
Ich hoffe Du kommst damit weiter.
https://www.herber.de/bbs/user/106322.xlsm
codebeschreibung beim code
Viele Grüße
Peter (hpo)

AW: Adressübernahme u. Anrede via ComboBox
17.06.2016 14:33:15
Lukas
Alles klar Peter.
Ich hab es kapiert und den Code wie folgt erweitert (Kundennr., Geschlecht in erste Zeile):
Private Sub ComboBox1_Click()
Dim intZeileNr As Integer
intZeileNr = ComboBox1.ListIndex + 2    'Hier wird die Zeilennummer ermittelt
With Sheets(1)  'Hier werden die Daten aus der Tabelle2 in die Tabelle1 eingetragen
.Range("B12").Value = Sheets(2).Cells(intZeileNr, 3).Value 'Institution
.Range("B12").Value = Sheets(2).Cells(intZeileNr, 4).Value 'Geschlecht Frau Herr
.Range("B13").Value = Sheets(2).Cells(intZeileNr, 5).Value & " " & Sheets(2).Cells( _
intZeileNr, 6).Value & " " & Sheets(2).Cells(intZeileNr, 7).Value 'Titel Vorname Nachname
.Range("B14").Value = Sheets(2).Cells(intZeileNr, 8).Value 'Strasse
.Range("B15").Value = Sheets(2).Cells(intZeileNr, 9).Value & " " & Sheets(2).Cells( _
intZeileNr, 10).Value 'PLZ Ort
.Range("I14").Value = Sheets(2).Cells(intZeileNr, 1).Value 'Kundennr.
.Range("B19").Value = Sheets(2).Cells(intZeileNr, 12).Value 'Anrede
End With
End Sub


Private Sub ComboBox1_DropButtonClick()
Dim i As Byte, strName As String
ComboBox1.Clear     'Leert die Combobox
For i = 2 To Sheets(2).Cells(Rows.Count, 7).End(xlUp).Row       'Die schleife durchläuft die  _
Zellen G2 bis G ende
strName = Sheets(2).Cells(i, 7).Value                       'Schreib den Inhalt der Zelle  _
in die Variable
If strName = "" Then strName = Sheets(2).Cells(i, 3).Value  'Prüft ob Variable leer ist,  _
wenn Ja dann Inhalt von C zeile
ComboBox1.AddItem strName                                   'schreib den Inhalt der  _
Variable in Combobox
Next i
End Sub
Meine Fragen:
1. Wenn Spalte Titel (5) leer ist habe ich vor dem Vornamen ein Leerzeichen drinnen. Muss da noch eine If-Abfrage dazu damit Excel das Leerzeichen nicht macht?
2. Seit ich die Zeile "B12" modifiziert habe zeigt mir die ComboBox keine Institutionen mehr an. ;(
Gruß
Erich

Anzeige
AW: Adressübernahme u. Anrede via ComboBox
17.06.2016 14:39:02
Lukas
ad 2.
Ideal wäre: Wenn Institution leer dann zeigte in der ComboBox immer den Namen der Institution an, egal ob in der selben Zeile der Vorname u. Nachname einer Kontaktperson eingetragen ist.

AW: Adressübernahme u. Anrede via ComboBox
17.06.2016 14:50:37
Lukas
ad 2.
Wenn die Adresse der Institution in Tabelle1 eingetragen wird, muss diese folgendes Adressformat haben:
B12: Institution
B13: Geschlecht+Vorname+Nachname (wenn in Tabelle2 keine Kontaktperson eingetragen ist soll die Zeile einfach leer bleiben)
B14: Straße
B15: PLZ Ort
Adressformat Privatperson:
B12: Geschlecht
B13: Titel Vorname Nachname
B14: Straße
B15: PLZ Ort
Gruß
Erich
ps: Sorry, dass ich jetzt drei Beiträge eröffnet habe. Hat sich beim Testen und praktischen Anwendung der Vorlage ergeben ;(

Anzeige
AW: Adressübernahme u. Anrede via ComboBox
17.06.2016 21:21:25
Peter
Hallo Erich (Lukas)
In der Combobox wird immer zuerst die Institution und nur wenn keine Institution eingetragen ist wird der Name eingetragen.
Achtung es muss für jeden Datensatz eine RE-Nr. eingegeben sein.
Die Adressen werden angepasst ausgegeben.
https://www.herber.de/bbs/user/106330.xlsm
Viele Grüße
Peter (hpo)

AW: Adressübernahme u. Anrede via ComboBox
20.06.2016 11:23:42
Lukas
Hallo Peter,
sry für die verspätete Antwort.
Die ComboBox funkt 100% so wie wir uns das vorgestellt haben.
Vielen DANK! Echt SUPER!
Dein Coding ist mir verständlich obwohl ich es mit meinen bescheidenen VBA Kenntnissen wahrscheinl. nicht hinbekommen hätte (vor allem was die genaue Syntax betrifft).
Ich habe die With-Schleife noch um RE- u. Kd-Nr. ergänzt.
Ich hätte noch ein letztes Anliegen:
Es gibt ein Umsatz Sheet(3) wo Kundendaten, Netto- u. Bruttobetrag aus Sheet(1) per Klick autom. eingetragen werden sollen.
Dazu habe ich einen Butten "Umsatz eintragen" erstellt und einen Sub gecoded. Leider trägt mir Excel keine Daten in Sheet 3 ein. Könntest du dir das kurz ansehen? Wahrscheinl. eine Kleinigkeit.
Zum Datum hätte ich noch eine Frage: In der Vorlage wird Rechnungsdatum mit =heute() ermittelt. Bleibt beim Übertrag in das Umsatzsheet das "echte" (tatsächliche) RE-Datum erhalten? (Anm.: Nachdem mit .Value übertragen wird gehe ich davon aus, bin aber nicht 100% sicher.)
https://www.herber.de/bbs/user/106352.xlsm
Gruß Erich
ps: Ich schreibe über den Acc. meines Kumpels weil meine Reg. leider nicht geklappt hat.

Anzeige
AW: Adressübernahme u. Anrede via ComboBox
20.06.2016 11:39:28
Lukas
Ergänzung ComboBox1:
Ich habe in der For-Next Schleife die Ausgabe noch um den Vornamen (i, 6) wie folgt ergänzt:
If strName = "" Then strName = Sheets(2).Cells(i, 7).Value & " " & Sheets(2).Cells(i, 6).Value 'Prüft ob Variable leer ist, wenn Ja dann Inhalt von G zeile
Ist eine alphabetische Sortierung in der ComboBox nach "Name" möglich?

AW: Adressübernahme u. Anrede via ComboBox
20.06.2016 11:42:39
Lukas
Bzw. auch nach Institution?
Quasi: "(G)emeinde" kommt in der DropDown-Liste vor "(M)ustermann".

AW: Adressübernahme u. Anrede via ComboBox
20.06.2016 13:42:26
Peter
Hallo Erich
Die Combobox wird sortiert angezeigt.
Der Umsatz kann in TB3 übernohmen werden.
Ich hoffe Du kommst damit klar
https://www.herber.de/bbs/user/106357.xlsm
Viele Grüße
Peter (hpo)

Anzeige
AW: Adressübernahme u. Anrede via ComboBox
20.06.2016 15:15:22
Lukas
Hallo Peter,
Ich/Wir DAAANKEN DIR von ganzem Herzen!!!!! Es funktioniert perfekt!
Ich werde die Funktionen heute Abend in die Vorlage für die Dienststelle einbauen u. gebe dir morgen Bescheid.
Ich habe abermals das Coding studiert. Ist jetzt komplexer geworden.
Mir ist nicht jede Stelle 100% klar.
Werde mich da noch der VBA-Hilfe bedienen müssen.
Trotzdem habe ich mit dieser Herausforderung wieder ein kleeeines Stück VBA dazugelernt.
Lieben Gruß
Erich & Lukas

AW: Adressübernahme u. Anrede via ComboBox
21.06.2016 08:51:06
Lukas
Guten Morgen Peter,
die Vorlage wird seit heute auf der Dienststelle verwendet ;-)
Ich habe noch eine Frage zur alphabetischen Sortierung innerhalb der ComboBox:
Ist die Sortierung direkt im VBA-Coding möglich? Also ohne den Zwischenschritt, Name und RE-Nr. in Spalte N u. O zu schreiben?
Private Sub ComboBox1_DropButtonClick()
Dim i As Byte, strName As String
Sheets(2).Cells(i, 14).Value = strName  'schreibt den Inhalt der Variable in die Spalte N
Sheets(2).Cells(i, 15).Value = Sheets(2).Cells(i, 2).Value  'schreibt die RENr in Spalte O
Next i
Call Sortieren  'Sortiert die Spalte N/O
ComboBox1.Clear     'Leert die Combobox
For i = 2 To Sheets(2).Cells(Rows.Count, 15).End(xlUp).Row
ComboBox1.AddItem Sheets(2).Cells(i, 14).Value  'schreibt die Spalte N in Combobox
Next i
End Sub
Gruß
Erich

Anzeige
AW: Adressübernahme u. Anrede via ComboBox
21.06.2016 09:13:17
Peter
Hallo Erich,
So viel ich weis ist dass nicht möglich, kann mich aber auch Täuschen.
Ich habe den zwischen schritt deshalb gewählt um die Daten eindeutig zu finden. Denn es ist vieleicht ja möglich, das ein Name mehrmals vorkommen kann z.B. Müller,Schmidt usw.
Wenn die Spalten N und O gebraucht werden, so kannst Du sie ja verschieben oder auch ausblenden.
Viele Grüße
Peter (hpo)

AW: Adressübernahme u. Anrede via ComboBox
21.06.2016 10:13:18
Lukas
Hallo Peter,
ich habe die Spalten inzw. nach R/S verschoben.
Unabhängig davon erhalte ich leider einen Laufzeitfehler 1004 wenn ich die drei Subs mit F5 ausführe.
Auch in deiner Originaldatei (soeben geprüft).
VBA bleibt bei
If Sheets(2).Cells(intZeileNr, 3).Value "" Then 'wenn Institution eingetragen = Adressformat Inst.
stehen. Anm.: In der originalen Vorlage sind noch 3 weitere Sheets (danach) enthalten. Könnte dies das Problem sein?
Ich glaube es wäre auch nicht schlecht wenn bei jedem Klick auf die ComboBox die beiden beschriebenen Spalten R/S vorher gelöscht werden. Ich habe 15 Zeilen mit Testkunden eingefügt und danach wieder gelöscht. In den beiden Sortierspalten blieben die Testkunden jedoch vorhanden. Ergo wurden sie dann auch noch in der DropDown angezeigt.

Anzeige
AW: Adressübernahme u. Anrede via ComboBox
21.06.2016 10:55:12
Lukas
Nach einigen Tests vermute ich, dass es an "Sheets(2)" liegt.
Offensichtl. findet Excel Sheets(2) in meiner Datei nicht obwohl sich Sheet2 an identer Position wie in deiner Datei befindet.
Ist es viell. besser den "Sheetnamen" zu verwenden?

AW: Adressübernahme u. Anrede via ComboBox
21.06.2016 11:57:51
Peter
Hallo Erich,
ich habe die Datei angepasst.
Du kannst aber auch alle Sheets mit dem Namen ansprechen.
z.B.
suche: Sheets(1)
ersetze: Sheets("Hier den Namen eintragen")
Du musst auch die Spalten Nr anpassen.
Ich hoffe Du kommst jetzt damit zurecht.
https://www.herber.de/bbs/user/106378.xlsm
Viele Grüße
Peter (hpo)

Anzeige
AW: Adressübernahme u. Anrede via ComboBox
21.06.2016 12:54:40
Lukas
Hallo Peter,
wiedermal T~a~u~s~e~n~d DANK für die Anpassungen.
Funkt einwandfrei.
Trotzdem gefällt mir die dynam. Variante mit Sheet (2) usf. besser.
Leider scheint Excel mit den Blattnummern durcheinander zu geraten obwohl die Blattnr., wie erwähnt, in beiden Dateien ident sind. ?!
Ich werde die Anpassungen heute Abend in der Vorlagendatei vornehmen u. dir morgen Bescheid geben.
Gruß Erich

AW: Adressübernahme u. Anrede via ComboBox
23.06.2016 13:07:28
Lukas
Hallo Peter,
sry f. die verspätete Antwort. Ich war gestern auf Schulung.
Ich habe in der Vorlagendatei jettz alle Sheets mit Namen definiert. Nun funkt es einwandfrei!
DANKE für den Tipp.
Zwei kleine Anregungen habe ich heute als Feedback erhalten:
1. Da mehrere User in der Vorlage (1File, keine Duplikate) arbeiten wurde die Frage gestellt ob es möglich ist, doppelt eingetragenen Umsatz zu verhindern?
Ich denke, dass dies über die Re-Nr.-Variable möglich sein müßte. Quasi: WENN RE-Nr. ("I13", intZeileNr2) bereits in Sheet UMSATZ eingetragen, dann MsgBox "Umsatz bereits vorhanden."
2. In der RE-Vorlage multipliziere ich in Spalte "Gesamt" einfach Anzahl*Preis/Einheit. Zieht man die Formel mit dem Fadenkreuz nach unten steht in den Leerzeilen (wenn Excel keine Werte findet) stets 0,00 €. Lässt sich die Formel erweitern, sodass Excel Leerzeilen ignoriert? Habe es mit WENN UND versucht, leider erfolglos. (Anm.: Es werden pro Position stets zwei Zeilen benötigt, gerechnet soll aber nur in der ersten Z. werden)
lG Erich
ps: Ich hoffe wir können den Thread nach dieser Sache erfolgreich schliessen. Geht ja schon über eine Woche und ich möchte dich keinesfalls weiter quälen. !!!

Links zu Excel-Dialogen

ComboBox

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige