VBA Button in Excel für aktuelle Adresse in ein anderes Tabellenblatt kopieren

[schnuff]

Hallo, Forum,

ich benötige eine Hilfestellung in Excel VBA.
Kurzer Sachverhalt, ich habe mir eine Userform Eingabemaske für Adressen mit einer Listbox angelegt.
Jetzt möchte ich, wenn ich einen aktuellen Eintrag aus einer Listbox wähle, (= nur der Name in der Listbox) über einen Button in ein anderes Tabellenblatt übernehmen.

Tabellenblatt Adressen: (Quelldatei der Adressedaten (Zelle A=Name; D=Straße; E=PLZ+F=Ort)
PLZ+Ort möchte ich in eine Zelle geschrieben.

Tabellenblatt Rechnung: =Zieldatei. Wunsch Name soll in A3 kopiert werden; Straße in A4; PLZ+Ort in A6)

Ich bin in VBA noch relativ unerfahren.
Für eine Hilfestellung im Voraus schon einmal vielen Dank.

MfG
schnuff

 

[Makkaroni]

Hier das Code-Fragment, dass du brauchst. Du erzeugst das Event AfterUpdate in der Listbox. Beim Anklicken des Eintrags wird dieser in die Zelle B2 im Arbeitsblatt "Sheet2" geschrieben.

Private Sub ListBox1_AfterUpdate()
Dim s As String
s = ListBox1.Value
ActiveWorkbook.Sheets("Sheet2").Activate
ActiveSheet.Range("B2").Select
ActiveSheet.Range("B2") = s
End Sub

 

[schnuff]

Hallo Makkaroni,

sorry das ich mich so spät melde. Dein Code funktioniert prima! Jedoch benötige ich zusätzlich den Transfer aus der Listbox von der Straße und der PLZ+Ort in (Sheet2) wie folgt.

Name: B2 = erledigt!
Straße: B3
PLZ+Ort: B5 (hier sind aber 2 Zellen die Quelle, PLZ aus sheet 1 Zeile D, der Ort aus Zeile F in sheet 1)

Könntest du mir den Code dementsprechend erweitern?

Für deine Mühe vielen Dank.

Gruß
schnuff

 

[Makkaroni]

Ich weiss nicht wie die Daten aussehen, die in der Listbox sind.

Angenommen ein Eintrag in der Listbox sieht so aus:
"Meier; Berliner Strasse; Berlin; 10010"

Dann zerteilst diesen String in 4 Werte s1, s2, s3 u. s4 u. schreibst ihn in die entsprechenden Zellen in Excel.
Demnach musst du nur deine Listbox so befüllen, dass sie alle Daten beinhaltet, die du benötigst.

 

[schnuff]

Hallo Makkaroni,
melde mich sehr spät, war ein paar Tage im Urlaub.
Ich versuche es nochmals strukturierter zu erklären.
Ich habe eine userform/adressen.
Feld 1: Listbox - zeigt nur den Vor + Nachnamen an. (diesen habe ich bereits im Tabellenblatt (Rechnung) in Zelle A3 angezeigt, beim Anklicken in der Listbox. Jetzt möchte ich, dass beim Anklicken des Vor/Nachnamens auch die u.a. Werte übernommen werden.
Feld 2: Adresse, diese benötige ich in einem neuen Tabellenblatt (Rechnung) in Zelle A4
Feld 3: PLZ, diesen benötige ich in einem neuen Tabellenblatt (Rechnung) in Zelle A6
Feld 4: Ort, diesen benötige ich ebenfalls in einem neuen Tabellenblatt (Rechnung) auch in Zelle A6, zusammen mit der PLZ

Wie müsste mein VBA Code heißen?

Gruß schnuff

 

[SOL Solutions]

Namen und Adresse trägst Du manuell in Dein UserForm ein? Wo ist der Vorteil gegenüber es direkt in den Zielbereich zu schreiben? Bisher macht es ja nichts anderes als von einem Ort an einen anderen zu Kopieren.

Was ist denn das Ziel der Aufgabe? Was soll das Tool am Ende können? Ich könnte mir vorstellen das man die Namen zusammen mit der Adresse in einem Sheet anlegt und wenn man dann den Namen im UserForm auswählt, die Adresse automatisch gezogen wird. Wie eine kleine Datenbank.

 

[schnuff]

Hallo SOL Solution,

ich befülle die Daten manuell in meine userform, dass an ein Tabellensheet verknüft ist. Damit habe ich die Daten für die Zukunft verfügbar und kann sie jederzeit in mein Rechnungsformular anwählen. Jedoch nur den Namen, nicht die Straße, PLZ, Ort. D. h. in der Listbox erscheint der Name, der direkt in mein Rechnungsformular verknüpft ist. Nun möchte ich noch Straße, PLZ, Ort, verknüpfen. Genau wie du erwähntest........wie eine Datenbank!
Wie müsste der Code dann lauten? (siehe #5)
Im Voraus vielen Dank für eine Hilfestellung.

 

[SOL Solutions]

Nagut, eigentlich hast Du den Code ja schon weiter oben von Makkaroni bekommen, Du musst ihn lediglich ein wenig anpassen. Das geht natuerlich nur wenn Du fuer die Adresse separate Listboxen/Textboxen hast. Wie von Makkaroni ebenfalls schon angemerkt, muessten wir wissen wie Deine Daten angeordnet sind. Bisher kann ich mir Dein UserForm noch nicht ganz vorstellen.

Poste doch mal eine Beispieldatei.

 

[schnuff]

Habe ein 2 Dateien angehängt.
1. Anhang = userform / Hier wäre mein Ziel, dass ich nach Markierung des Eintrags in der Listbox (Name) auch die Adresse, PLZ, Ort, mit in mein Rechnungsformular übernommen bekomme.
2. Anhang = ist die Quelldatei der Adressen für die userform.

Mein jetziger VBA Code lautet wie folgt!
Private Sub ListBox1_AfterUpdate()
Dim s As String
s = ListBox1.Value
ActiveWorkbook.Sheets("Rechnung").Activate
ActiveSheet.Range("A3").Select
ActiveSheet.Range("A3") = s
End Sub

Nochmals vielen Dank für deine Mühe.
Gruß schnuff

 

[SOL Solutions]

Zuallererst, schickes Rechnungstool. Ich mag das Design. Zweitens, für das was Du vor hast, gibt es keine sehr einfachen Lösung. Du müsstest jetzt in Deinen vorhandend Code für die Listbox eine Schleife einbauen, welche nach dem Namen im AdressBlatt sucht und dann die einzelnen Spalten auf Deine Textboxen verteilt.

Sowas wie: TextBoxStrasse = cells(ZeileWoDerNameSteht, SpalteWoDieStrasseSteht)

Usw.

Es gibt natürlich auch noch andere Lösungen via SQL zum Beispiel, aber das sprengt dann vielleicht doch den Rahmen ein wenig.

Wie heißen denn Deine Textboxen, vielleicht kann ich was kleines zusammen schustern

 

[schnuff]

Meine Textboxen lauten wie folgt:
TextBox1 = Name
TextBox4 = Adresse
TestBox5 = PLZ
TextBox6 = Ort

 

[SOL Solutions]

Noch eine kurze Frage, die anderen Knöpfe funktionieren alle und machen was?

 

[Makkaroni]

Hallo schnuff,

mein Vorschlag wäre die Listbox so zu befüllen, dass sie alle Daten beinhaltet:
Ein Eintrag würde dann z.B. so aussehen:
Harry Hirsch;Harry.Hirsch@outlook.de;08342 665588;Müllergasse;87616;Marktoberdorf;

Wenn dich die Anzeige von so vielen Informationen in der Listbox stört, kannst du nach dem Namen noch eine leere dummy Spalte mit anzeigen. Dann sieht man diese Informationen nicht, aber sie sind vorhanden.

Hier der Code zum Befüllen der Listbox
Private Sub FillListbox()
Dim i As Integer, j As Integer, iStart As Integer, iEnd As Integer, s As String
Dim ws As Worksheet
'String Structure
'Harry Hirsch;Harry.Hirsch@outlook.de;08342 665588;Müllergasse;87616;Marktoberdorf;
'1=Name;2=Email;3=Telefon;4=PLZ;5=Strasse;6=Ort

' Startcell is A2, Ende der Daten ist hier hardcoded, muss bestimmt werden
iStart = 2
iEnd = 4
Set ws = ActiveWorkbook.Sheets("Adressen")
For i = iStart To iEnd
For j = 1 To 6
s = s & ws.Cells(i, j) & ";"

Next j
' strip semicolon
If s <> "" Then s = Left(s, Len(s) - 1)
' Add to Listbox
Me.ListBox1.AddItem s
s = ""
Next i
End Sub

Dann kannst du deine Felder nach Auswahl eines Listeneintrags in der Listbox wie folgt befüllen:

Private Sub ListBox1_AfterUpdate()
Dim s As String, s1 As String, s2 As String
s = ListBox1.Value

ActiveWorkbook.Sheets("Rechnung").Activate
' Get Name
s1 = GetFromString(s, "Name")
ActiveSheet.Range("A3").Select
ActiveSheet.Range("A3") = s1
' Get Adresse
s1 = GetFromString(s, "Strasse")
ActiveSheet.Range("A4").Select
ActiveSheet.Range("A4") = s1
' Get PLZ + Ort
s1 = GetFromString(s, "PLZ")
s2 = GetFromString(s, "Ort")
s1 = s1 & " " & s2
ActiveSheet.Range("A6").Select
ActiveSheet.Range("A6") = s1

End Sub

Hierbei hab ich eine Funktion zur Bestimmung des Eintrages geschrieben:

Public Function GetFromString(s As String, myField As String) As String
Dim i As Integer, a, ret As String
'String Structure
'Harry Hirsch;Harry.Hirsch@outlook.de;08342 665588;Müllergasse;87616;Marktoberdorf;
'1=Name;2=Email;3=Telefon;4=Strasse;5=PLZ;6=Ort
a = Split(s, ";")
ret = ""
For i = 0 To UBound(a) - 1
Select Case myField
Case "Name"
ret = a(0)
Case "Email"
ret = a(1)
Case "Telefon"
ret = a(2)
Case "Strasse"
ret = a(3)
Case "PLZ"
ret = a(4)
Case "Ort"
ret = a(5)
End Select
Next i
GetFromString = ret
End Function

 

[schnuff]

Ja, alle Knöpfe haben eine Funktion und sind auch betriebsbereit.

 

[Makkaroni]

Ach ja zur Sortierung in der Listbox:

Hier ist mein Tipp die Daten im Arbeitsblatt "Adressen" immer sortiert abzuspeichern, dann erscheinen diese auch immer so in der Listbox

 

[schnuff]

Hallo Makkaroni,

zuerst einmal vielen Dank für deine Unterstützung und Hilfestellung meines Lösungsansatzes, du hast dir sehr viel Arbeit gemacht. Ich bringe deinen Code jedoch nicht zum Laufen! Vlt. muß ich deinen Code ja noch irgendwo anpassen, jedoch "I dont no where" ?
Ich habe deinen Code wie folgt in meine userform eingefügt. (siehe Anhang)


Vielen Dank im Voraus für einen weiteren Lösungsvorschlag.
LG schnuff

 

[Janush]

Hast Du dir all Deine Funktionen bisher mit dem Makrorekorder zusammen geklickt? Auch ne Möglichkeit, aber manchmal reicht das dann nicht mehr aus.

Benutzt Du das Tool selbst, oder bastelst Du das für jemanden zusammen?

 

[schnuff]

Hallo Janush, ich habe mir die Funktionen aus dem Netz mit einzelnen Bausteinen zusammengebastelt. Den Makrorecorder habe ich nur für meine Schaltflächen benutzt. Wieso, ist das wichtig für dich ? Achja, das Tool benützt zukünftig unser Schulsekretariat.

 

[Janush]

Nein nein, hab mich nur über die vielen Module gewundert Hab mich eh gefragt warum Du den Code für die Listbox brauchst, wenn Du doch den Rest so hinbekommen hast. "Ablage Rechnung" und "Datensatz suchen", gehen doch in die selbe Richtung, oder?

 

[Makkaroni]

Hallo Makkaroni,

... Ich bringe deinen Code jedoch nicht zum Laufen! Vlt. muß ich deinen Code ja noch irgendwo anpassen, jedoch "I dont no where" ?
Ich habe deinen Code wie folgt in meine userform eingefügt. (siehe Anhang)
Anhang anzeigen 589762
...

LG schnuff

Es fehlt der Code für die Funktion "GetFromString".
Du musst die Funktion entweder in eines deiner Module kopieren oder aber direkt in die bestehenden Funktionen des Forms. In deinem Fall würde ich es in das Form kopieren, da du keine Modulstruktur hast. Trotzdem solltest du verstehen, was du machst, sonst kommt irgendwann der Punkt wo du nicht mehr durchblickst.

In diesem Fall ist die Funktion "GetFromString" als Public deklariert. D.h. du kannst sie von allen Modulen aus aufrufen.

Public Function GetFromString(s As String, myField As String) As String
Dim i As Integer, a, ret As String
'String Structure
'Harry Hirsch;Harry.Hirsch@outlook.de;08342 665588;Müllergasse;87616;Marktoberdorf;
'1=Name;2=Email;3=Telefon;4=Strasse;5=PLZ;6=Ort
a = Split(s, ";")
ret = ""
For i = 0 To UBound(a) - 1
Select Case myField
Case "Name"
ret = a(0)
Case "Email"
ret = a(1)
Case "Telefon"
ret = a(2)
Case "Strasse"
ret = a(3)
Case "PLZ"
ret = a(4)
Case "Ort"
ret = a(5)
End Select
Next i
GetFromString = ret
End Function