Datenschutzerklärung


Direktnachricht



Ihre Software
Details
Excel/VBA 🔍
Add-Ins

Suche in Beispielen und Tipps zu Excel und VBA

Suchbegriff(e) mit Leerzeichen getrennt:

Anleitung: Zelldaten in Userform und zurückMakro/Sub/ProzedurTipp

Kategorie: Steuerelemente ▸ Userform

(Tipp 76) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich Daten aus Zellen in eine Userform-Listbox einlesen lassen, die zugehörigen Angaben beim Klick auf die Listbox in Textboxen anzeigen lassen und Änderungen in der Userform in die Zellen schreiben lassen?

Bei der Arbeit mit Userforms gibt es immer verschiedene Wege, die zu einem Ziel führen. Die Kunst ist immer, Techniken/Konzeptionen zu wählen, die den eigenen Anforderungen am besten entsprechen; die am sichersten sind, möglichst kurze Laufzeiten aufweisen und Ressourcen am besten schonen. Darum soll es hier aber nicht gehen. An dieser Stelle stehen grundsätzliche Möglichkeiten im Mittelpunkt, die aufzeigen, wie die Aufgabenstellung prinzipiell gelöst werden kann. Auf Fehlerbehandlungen, Ergonomie und Schönheit wird hier deshalb verzichtet.

Für das Beispiel wird die abgebildete kleine Tabelle auf dem Blatt Daten verwendet; funktionieren soll dann die Userform1 mit den folgenden grundsätzlichen Elementen:

  • Listbox1 (das wird die Obstliste)
  • TextBox1
  • TextBox2
  • TextBox3
  • TextBox4
  • CommandButton1 (Neu)
  • CommandButton2 (Schließen)

Tipp: Auch wenn wir hier die Standardbezeichnungen der Elemente verwenden - übersichtlicher ist es, wenn Sie jedem Element eine aussagekräftige Eigenschaft bei Name vergeben. Commandbutton1 könnte so zum Beispiel cmdNeu genannt werden.

Nun müssen wir uns schon entscheiden: Der Code kann in ein allgemeines Modul (Modul1, Modul2, …) oder in diesem Fall in das Klassenmodul der Userform. Nehmen wir ein allgemeines Modul, muss im Code immer die gesamte Userform angesprochen werden, denn allgemeine Module stehen für die gesamte (in diesem Fall) Mappe - wenn mit mehreren Userforms gearbeitet würde, müsste der Code dort wissen, welche Userform angesprochen werden soll. Wir entscheiden uns hier für das Klassenmodul der Userform, das konkret dieser Userform zugeordnet ist.

Klassenmodul der Userform: Basis für den Code

In das Modul der Userform gelangt man am einfachsten und schnellsten, indem man auf das Element, für das der Code erstellt werden soll, doppelklickt. Wir wollen zuerst einmal dafür sorgen, dass die Userform per Klick auf Schließen geschlossen werden kann. Dazu also ein Doppelklick auf den Button (Commandbutton2 war das). Wir gelangen in das Klassenmodul, haben schon den vorbereiteten Code, der für das Klicken auf den Button zuständig ist und tragen dort einfach Unload Me ein:

Private Sub CommandButton2_Click() Unload Me End Sub

Mit F5 kann das auch gleich getestet werden Userform aufrufen und Button anklicken.

Einlesen der Daten aus der Tabelle

Der nächste Schritt ist das Einlesen der Daten von der Tabelle in die Listbox. Dies soll geschehen, wenn die Userform aufgerufen, also geladen wird.

Dazu wählen wir - wenn es noch nicht eingestellt ist - links oben das Element, die Userform. Rechts wählen wir das Ereignis, also wann es geschehen soll. Das ist hier Initlialize.

Wenn wir das gewählt haben, steht auch schon der Code dafür da, den wir vervollständigen. Im ersten Beispiel wählen wir die gebundene Form - die Daten erscheinen in der Listbox genau so, wie sie in der Tabelle stehen:

Private Sub UserForm_Initialize() ListBox1.RowSource = "Daten!B2:B10" End Sub

Manchmal kann es jedoch notwendig sein, die Daten ungebunden in die Liste einzutragen, wenn zum Beispiel Einträge ergänzt werden sollen. Im folgenden Beispiel wird dies gezeigt, indem die Daten aus Spalte A vorangestellt werden. Verwendet werden hier die Daten von Zeile 2 bis zur letzten in Spalte 2 ausgefüllten Zelle:

Private Sub UserForm_Initialize() 'ListBox1.RowSource = "Daten!B2:B10" Dim lngZ As LongPtr With Sheets("Daten") For lngZ = 2 To .Cells(Rows.Count, 2).End(xlUp).Row ListBox1.AddItem .Cells(lngZ, 1) & " - " & .Cells(lngZ, 2) Next End With End Sub

Im zweiten Beispiel sind die Daten ungebunden in der Liste - ändern sich die Daten in der Tabelle, bleibt die Listbox so, wie sie erstellt wurde.

Details zu gewähltem Listeneintrag anzeigen

Nun können wir unsere Einträge in der Liste anklicken, aber es passiert natürlich noch nichts. Dass rechts in den Textboxen die Details zum angeklickten Eintrag erscheinen, müssen wir erst eingeben. Es soll etwas beim Anklicken der Listbox passieren - also doppelklicken wir im Editor doppelt auf die Listbox und haben den Rahmen für den Code:

Private Sub ListBox1_Click() End Sub

Zunächst einmal ist wichtig, aus welcher Zeile des Tabellenblattes die Daten kommen sollen, wenn wir auf einen Eintrag in der Listbox klicken.

Dazu nutzen wir die Listindex-Eigenschaft der Listbox. Wenn wir in einer Listbox einen Eintrag auswählen, hat der immer einen Listindex, beginnend mit 0. Ist kein Eintrag ausgewählt, ist der Listindex -1.

Wenn wir also den ersten Eintrag anklicken, sollen die Daten aus der Zeile 2 erscheinen, beim zweiten Eintrag die Daten aus Zeile 3 usw. Wir addieren also zum Listindex einfach 2 und verwenden für die einzelne Textbox den jeweiligen Eintrag aus den Spalten 1 bis 4. Das kann so aussehen:

Dim intIndex As Integer intIndex = ListBox1.ListIndex With Sheets("Daten") TextBox1.Text = .Cells(intIndex + 2, 1) TextBox2.Text = .Cells(intIndex + 2, 2) TextBox3.Text = .Cells(intIndex + 2, 3) TextBox4.Text = .Cells(intIndex + 2, 4) End With

Nun kann allerdings der Fall eintreten, dass mal kein Eintrag gewählt ist. Dann sollen die Textboxen leer sein. Dafür verwenden wir hier eine kleine Schleife - da wir das später nochmal benötigen, lagern wir das in eine separate Sub() aus, die wir dann einfach aufrufen:

Sub TextBoxenLeer() Dim ctrControl As Control For Each ctrControl In Me.Controls If ctrControl.Name Like "TextBox*" Then ctrControl.Text = "" Next End Sub

Damit vervollständigen wir den Code der Listbox, so dass er so aussieht und wir es mit z. B. F5 testen können:

Private Sub ListBox1_Click() Dim intIndex As Integer intIndex = ListBox1.ListIndex If intIndex >= 0 Then With Sheets("Daten") TextBox1.Text = .Cells(intIndex + 2, 1) TextBox2.Text = .Cells(intIndex + 2, 2) TextBox3.Text = .Cells(intIndex + 2, 3) TextBox4.Text = .Cells(intIndex + 2, 4) End With Else TextBoxenLeer End If End Sub

Änderungen aus der Userform in die Tabelle übernehmen

Nun sollen Änderungen, die in den Textboxen vorgenommen werden, auch in die Tabelle übernommen werden. Hierbei ist es besonders wichtig, wie man vorgeht - bei vier Textboxen ist das sicher kein Problem, aber bei größeren Datenmasken kann bei einer unzweckmäßigen Technik schon viel überflüssiger Code entstehen. Für den Anfang sei hier jedoch nur auf das Erstellen einer separaten Klasse verwiesen oder auch auf das Verwenden eines Frames und das komplette Übernehmen mit einem Klick. Der Einfachheit halber weisen wir das Übernehmen hier den Textboxen direkt zu.

Damit wir nicht bei jeder Textbox überflüssigen Code wiederholen müssen, bereiten wir eine Sub() vor, die wir dann bei den Textboxen nur noch aufrufen. Die Sub() macht nichts anderes, als einen Wert in die Zelle lngZeile, lngSpalte zu schreiben:

Sub DatenInTabelle(ByVal lngZeile As LongPtr, ByVal lngSpalte As LongPtr, varWert) If lngZeile >= 2 Then Sheets("Daten").Cells(lngZeile, lngSpalte) = varWert End Sub

Damit das bei Änderungen in den Textboxen geschieht, müssen wir dazu noch den Code erstellen - auch hier wieder durch Doppelklick auf die einzelnen Textboxen. Dadurch erhalten wir die Code-Rahmen für die Boxen und fügen nur noch den Verweis auf die Sub ein, die wir gerade erstellt haben. Das sieht dann so aus:

Private Sub TextBox1_Change() DatenInTabelle ListBox1.ListIndex + 2, 1, TextBox1.Text End Sub Private Sub TextBox2_Change() DatenInTabelle ListBox1.ListIndex + 2, 2, TextBox2.Text End Sub Private Sub TextBox3_Change() DatenInTabelle ListBox1.ListIndex + 2, 3, TextBox3.Text End Sub Private Sub TextBox4_Change() DatenInTabelle ListBox1.ListIndex + 2, 4, TextBox4.Text End Sub

Wir sehen, dass auch hier für die Angabe der Zeile der Listindex der Listbox verwendet wird. Da der beim ersten Eintrag 0 ist, die Tabelle aber bei Zeile 2 beginnt, müssen wir hier noch die 2 addieren. Für den Fall, dass mal kein Eintrag in der Listbox gewählt ist (entspricht Listindex = -1), haben wir in Sub DatenInTabelle() noch die Abfrage If lngZeile >= 2 Then .

Damit sollte eine Änderung bei einem gewählten Eintrag übernommen werden. Da es hier um das Prinzip geht, lassen wir hier das Aktualisieren der Listeneinträge bei Änderungen der Spalten 1 und 2 außen vor. Erwähnt sei nur, dass dies einfach mit ListBox1.List(ListBox1.ListIndex) = möglich ist.

Neuer Eintrag

Fehlt eigentlich nur noch eins: Wie fügt man einen neuen Eintrag hinzu?

In diesem Fall machen wir es ganz einfach: Wir lassen in die erste leere Zelle in Spalte 2 (B) und in die Listbox einen Eintrag vornehmen und diesen in der Listbox auswählen. Der Rest funktioniert dann bereits.

Private Sub CommandButton1_Click() Dim lngZ As LongPtr With Sheets("Daten") lngZ = .Cells(Rows.Count, 2).End(xlUp).Row + 1 .Cells(lngZ, 2) = "Neuer Eintrag" End With ListBox1.AddItem "Neuer Eintrag" ListBox1.ListIndex = ListBox1.ListCount - 1 End Sub

Der Nachteil ist, dass die Liste dann diesen Eintrag behält, aber das lässt sich leicht beheben. Am einfachsten schreibt man den Code aus Einlesen der Daten aus der Tabelle in eine separate Sub und lässt die beim Aufruf der Userform und beim Erstellen eines neuen Eintrags ausführen.

Viel Erfolg!

Anzahl der Einträge in einer SpalteFormellösung

Kategorie: Tabelle ▸ Zellen

(Tipp 185) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich die Anzahl der Einträge in einer Spalte feststellen?

=ANZAHL2(A1:A9999)

Bei Eingabe in eine Zelle (Worksheet_Change)Makro/Sub/Prozedur

Kategorien: Tabelle ▸ Zellen und Ereignisse ▸ Tabellen

(Tipp 153) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich erreichen, daß bei einer Eingabe in eine Zelle andere Eintragungen automatisch erfolgen?

Grundsätzlich

  • Mit Alt und F11 in den VBA-Editor wechseln,
  • im Projekt-Explorer auf die Tabelle doppelklicken, in der die Eingaben vorgenommen werden,
  • dort entsprechenden Code eingeben.

Fragen treten oft auf, wenn es Endlosschleifen gibt, wenn Code etwas in einen Bereich einträgt, der durch einen Eventhandler überwacht wird. Die Änderung löst das Event aus, wodurch Code aufgerufen wird, der dann wieder etwas einträgt, was dann wieder das Event auslöst usw.

In solchen Fällen bietet sich an, vor dem Vornehmen von Änderungen die Events/Ereignisse auszuschalten und danach gleich wieder zu aktivieren:

Application.EnableEvents = False '… Code … Application.EnableEvents = True


Datum über der Eingabezelle eintragen

Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Row = 2 Then Cells(Target.Row - 1, Target.Column) = Date End Sub


Wert auf anderes Blatt in erste leere Zelle eintragen

Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim lngZ As LongPtr If Target.Address <> "$B$1" Then Exit Sub lngZ = Sheets(2).Cells(Rows.Count, 4).End(xlUp).Row Sheets(2).Cells(lngZ + 1, 4) = Range("b1") End Sub

Es wird zunächst geprüft, ob die Eingabezelle wirklich B1 ist.

Anschließend wird die Zeile der letzten ausgefüllten Zelle auf Blatt 2 in Spalte 4 (D) festgestellt. Dort wird die Eingabe in die nächste Zeile (lngZ + 1) eingetragen.


Bei Änderungen ungerade Spalten ausschließen

Frage: Wie kann ich bei der Änderung einer Auswahl in den Spalten 3 bis 49 abfragen, ob es sich um eine ungerade Spalte handelt?

Die Routine fragt ab, ob es sich bei der ausgewählten Spalte um eine Spalte handelt, bei deren Division durch 2 der Rest 1 ergibt.

Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Column > 2 And .Column < 50 And .Column Mod 2 = 1 Then Exit Sub End With 'Hier geht's weiter End Sub

Daten von mehreren Blättern zusammenfassenMakro/Sub/Prozedur

Kategorie: Mappe ▸ Tabellen

(Tipp 115) Nachricht zum Beitrag an Autor Nach oben

Ich habe mehrere Blätter mit Daten. Alle Blätter haben unterschiedlich viele Datensätze (Datenzeilen). Alle Daten sollen nacheinander auf einem Blatt zusammengefaßt werden.

Der Code löscht zunächst das Blatt Zusammenfassung, falls es existiert. Alternativ können natürlich auch Zellen oder Zellinhalte dieses Blattes gelöscht werden.

Anschließend läuft im Beispiel eine Schleife über die ersten drei Tabellenblätter. Von denen werden die Zellen in den Spalten 1 bis 5 bis zur letzten (in Spalte 1) ausgefüllten Zeile genommen und untereinander auf das Blatt Zusammenfassung kopiert.

Sub speichern() Dim lngZeile As Long, lngLetzteZ As Long, bytI As Byte Dim strBereich As String On Error Resume Next Worksheets("Zusammenfassung").Delete 'Auswertungsblatt löschen On Error GoTo 0: Err.Clear Worksheets.Add after:=Sheets(Sheets.Count) 'Auswertungsblatt einfügen ActiveSheet.Name = "Zusammenfassung" 'Variable, die dafür sorgt, daß die zusammengefaßten Daten untereinander stehen lngZeile = 1 'hier ab Zeile 1 For bytI = 1 To 3 'Von Blatt 1 bis Blatt 3 zusammenfassen lngLetzteZ = Worksheets(bytI).Cells(Worksheets(bytI).Rows.Count, 1).End(xlUp).Row strBereich = Range(Cells(1, 1), Cells(lngLetzteZ, 5)).Address 'Datenbereich (hier bis Spalte 5) auswählen Worksheets(bytI).Range(strBereich).Copy Sheets("Zusammenfassung").Cells(lngZeile, 1) 'Variable Zeile erhöhen lngZeile = lngZeile + lngLetzteZ Next End Sub

Erste leere Zelle (VBA + Formel)Makro/Sub/ProzedurFormellösung

Kategorie: Tabelle ▸ Zellen

(Tipp 13) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich einen Zellwert in die erste leere Zelle eines Bereiches eintragen lassen?

Die Beispiele zeigen, wie die letzte ausgefüllte Zelle zu ermitteln ist. In jedem Fall muss dann nur Zeile oder Spalte um 1 erhöht werden, um auf die erste leere Zelle zu kommen.

VBA

Grundsätzlich:

Mit Cells(Rows.Count, 1).End(xlUp).Row können wir abfragen, welche die letzte ausgefüllte Zelle in Spalte A (also 1) ist.

Brauchen wir die letzte ausgefüllte Zelle in einer Zeile, also die Spaltenangabe dieser Zelle, können wir Cells(1, Columns.Count).End(xlToLeft).Column verwenden (hier für Zeile 1).


Diese beiden Codestücke funktionieren auch zuverlässig, wenn nicht gerade die erste Zelle in der Spalte bzw. in der Zeile leer ist. In dem Fall würde auch die 1 zurückgegeben - was hieße, dass diese erste Zelle Inhalt hätte. Aus diesem Grund muss, wenn dies eine Rolle spielen kann, vorher geprüft werden, ob die erste Zelle der Spalte/Zeile auch leer ist. Damit wird dies berücksichtigt:

Dim strAddr As String 'Spalte A, entspricht 1: If IsEmpty(Cells(1, 1)) Then strAddr = Cells(1, 1).Address Else strAddr = Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).Address End If Range(strAddr).Value = "Erste leere Zelle"

Stehen in der ersten Zeile grundsätzlich Spaltenüberschriften, spielt das bei der ersten ausgefüllten Zelle in einer Spalte natürlich keine Rolle.


Das Eintragen erfolgt dann einfach in die nächste Zelle. Entweder:

Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Select

oder:

a = Cells(Rows.Count, 2).End(xlUp).Row + 1

Entsprechend ist es auch bei der ersten leeren Zelle in einer Zeile:

a = Cells(2, Columns.Count).End(xlToLeft).Column + 1

Wenn etwas in die erste freie Zelle in einer Zeile kopiert werden soll, kann man z. B. so vorgehen:

Sub NaechstfreieSpalte() Selection.Copy Destination:=Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Offset(0, 1) End Sub


Formel

Mit der Funktion ADRESSE() in Verbindung mit ANZAHL2() kann die letzte ausgefüllte Zelle ermittelt werden, wenn sich keine leeren Zellen im Bereich befinden:

=ADRESSE(ANZAHL2(B1:B34)+2;2;4)

Das +2 bedeutet, dass der zu prüfende Bereich erst in B3 beginnt.

Oder:

=INDIREKT("B"&ANZAHL2(B2:B10000)+1)

Das +1 heißt, dass der erste Wert in B in Zeile 2 steht. Steht der erste Wert in Zeile 1, kann es weggelassen werden. Steht der erste Wert aber in Zeile 3, wäre es +2 usw.

Eine andere Variante, die aber nur bis Spalte Z funktioniert, ist:

=LINKS(ADRESSE(ZEILE($B$6);SPALTE($B$6));3)&ANZAHL2($B$3:$B$65536)+ZEILE($B$3)-1

Variablen Bereich markierenMakro/Sub/Prozedur

Kategorie: Tabelle ▸ Selection

(Tipp 145) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich die nächste leere Spalte nach rechts in der Höhe einer Datenbank markieren?

Hier gibt es viele Möglichkeiten.

Der folgende Code stellt die Zeile der letzten ausgefüllten Zelle in Spalte A (1) und die Spalte der letzten ausgefüllten Zelle in Zeile 1 fest. Anschließend wird genau eine Spalte in der Höhe dieser Daten markiert:

Dim lngZ As LongPtr, lngS As LongPtr lngZ = Cells(Rows.Count, 1).End(xlUp).Row lngS = Cells(1, Columns.Count).End(xlToLeft).Column Range(Cells(1, lngS + 1), Cells(lngZ, lngS + 1)).Select

Auch UsedRange kann ggf. gut eingesetzt werden. Hier wird neben dem UsedRange ein Bereich markiert, der genau so breit ist wie der UsedRange:

Dim lngS As LongPtr lngS = ActiveSheet.UsedRange.Columns.Count Range(ActiveSheet.UsedRange.Address).Offset(0, lngS).Select

Die Frage ist natürlich, warum markiert werden soll. Man kann die Elemente auch direkt ansprechen, dann zappelt auch der Bildschirm nicht so.