Datensatz auslesen unter Nutzung einer ListBox

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
Bild

Betrifft: Datensatz auslesen unter Nutzung einer ListBox
von: Dirk Röseler
Geschrieben am: 28.04.2015 00:17:30

Hallo zusammen,
ich erstelle zur Zeit ein Formular für die Arbeit und habe ein Problem (bzw. eine Herausforderung).
Ich habe zwei UserForms, wobei die erste (UserForm1) für das Erfassen von Datensätzen vorgesehen ist. Alle Mitarbeiter nutzen hierfür eine gemeinsame Zieltabelle, wo also die entsprechenden Daten zusammengeführt werden.
In dieser UserForm gibt es nun die Möglichkeit, einen bereits zuvor erstellten Datensatz zu suchen und einzulesen (es können mehr als zwei davon von unterschiedlichen Mitarbeitern sein).
Klicke ich also auf den entsprechenden CommandButton auf der UserForm1, so öffnet sich eine UserForm2 mit Suchfeld und einer ListBox. Die Herausforderung hierbei ist nun die, dass die Zieltabelle 27 Spalten besitzt und ich nun in Spalte 5 einen Wert suche und danach alle Zeilen (mit 27 Spalten) mit diesem Wert in der ListBox anzeigen möchte. Anschließend möchte ich eine Zeile markieren und alle Spalten dieser Zeile in UserForm1 einlesen (übergeben), so dann hier alle Felder vorbefüllt werden.
Da es mit .AddItem nicht geht (max. 10 Spalten), muss ein Array her. Hier jedoch enden meine Kenntnisse. Da meine Testversion nur 6 Spalten hatte, klappte es hier wunderbar; bei den 27 Spalten dagegen hört es leider auf.
Vielleicht kann mir hier einer weiterhelfen?
BTW: UserForm1 (also für Datenerfassung) besitzt verscheiden TextBoxen, ComboBoxen sowie Checkboxen. Letztere werden in der Tabelle wiederum als JA und NEIN erfasst.
VG Dirk

Bild

Betrifft: AW: Datensatz auslesen unter Nutzung einer ListBox
von: Nepumuk
Geschrieben am: 28.04.2015 08:10:10
Hallo,
du musst die Daten in ein Array einlesen und diese dann an die ListBox übergeben.
Gruß
Nepumuk

Bild

Betrifft: AW: Datensatz auslesen unter Nutzung einer ListBox
von: Dirk Röseler
Geschrieben am: 28.04.2015 09:15:57
Sorry, meinst du, dass ich erst alle Daten der Tabelle erst einlesen muss oder kann ich hier bereits nur die zu suchenden Zeilen übergeben?
Hier übrigens mal mein Test, welcher - aufgrund weniger Spalten - perfekt mit .AddItem funktioniert:
Option Explicit

Private Sub AutoListBox()
    Workbooks.Open "C:\Users\Dirk\Documents\TEST_UserForm\TEST_DATEN.xlsx"
    Dim wks As Worksheet
    Set wks = Worksheets("DATEN")
    Dim rngSource As Object
    Dim intColums As Integer
    Dim rngCell As Range
    Dim strFirstAddress As String
    With wks.Range("D:D")
        Me.ListBox1.Clear
        ListBox1.Tag = 1
        Set rngSource = Range("A1").CurrentRegion
        Set rngSource = rngSource.Offset(1, 0).Resize(rngSource.Rows.Count - 1, rngSource. _
Columns.Count)
        intColums = rngSource.Columns.Count
        Set rngCell = .Find(Me.TextBox1.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not rngCell Is Nothing Then
            strFirstAddress = rngCell.Address
        Do
            With Me.ListBox1
                .ListStyle = fmListStyleOption ' Auswahlfeld zu Beginn der Zeile
                .ColumnCount = intColums
                '.ColumnWidths = ("2,0cm;1,5cm;1,0cm;2,0cm;2,0cm;1,5cm;1,5cm;1,0cm;2,5cm")
                .ColumnHeads = True
                .AddItem
                .List(.ListCount - 1, 0) = rngCell.Offset(0, -3).Value '1. Spalte
                .List(.ListCount - 1, 1) = rngCell.Offset(0, -2).Value '2. Spalte
                .List(.ListCount - 1, 2) = Format(rngCell.Offset(0, -1).Value, "hh:mm") '3.  _
Spalte
                .List(.ListCount - 1, 3) = rngCell.Value 'Suchspalte | 4. Spalte
                .List(.ListCount - 1, 4) = rngCell.Offset(0, 1).Value '5. Spalte
                .List(.ListCount - 1, 5) = rngCell.Offset(0, 2).Value '6. Spalte
                .List(.ListCount - 1, 6) = rngCell.Offset(0, 3).Value '7. Spalte
                .List(.ListCount - 1, 7) = Format(rngCell.Offset(0, 4).Value, "#,##0.00 EUR") ' _
8. Spalte
                .List(.ListCount - 1, 8) = rngCell.Offset(0, 5).Value '9. Spalte
            End With
        Set rngSource = Nothing
        ListBox1.Tag = ""
        Set rngCell = .FindNext(rngCell)
        Loop While Not rngCell Is Nothing And rngCell.Address <> strFirstAddress
        Else
            MsgBox "ID nicht gefunden", 48
        End If
    End With
    Application.DisplayAlerts = False
        Workbooks("TEST_DATEN.xlsx").Close SaveChanges:=False
    Application.DisplayAlerts = True
End Sub

Private Sub CommandButton1_Click()
    AutoListBox
End Sub

Private Sub CommandButton2_Click()
    With Me.ListBox1
        UserForm1.TextBox1 = .List(.ListCount - 1, 3) 'Suchspalte | 4. Spalte
        UserForm1.TextBox2 = .List(.ListIndex, 4) '5. Spalte
        UserForm1.ComboBox1 = .List(.ListIndex, 5) '6. Spalte
        UserForm1.ComboBox2 = .List(.ListIndex, 6) '7. Spalte
        UserForm1.TextBox3 = .List(.ListIndex, 7) '8. Spalte
        UserForm1.TextBox4 = .List(.ListIndex, 8) '9. Spalte
    End With
    Unload Me
End Sub
------
Also, wie stelle ich das nun konret um, damit das auch mit 27 Spalten klappt.
VG Dirk

Bild

Betrifft: AW: Datensatz auslesen unter Nutzung einer ListBox
von: Dirk Röseler
Geschrieben am: 28.04.2015 09:20:14
Option Explicit

Private Sub AutoListBox()
    Workbooks.Open "C:\Users\Dirk\Documents\TEST_UserForm\TEST_DATEN.xlsx"
    Dim wks As Worksheet
    Set wks = Worksheets("DATEN")
    Dim rngSource As Object
    Dim intColums As Integer
    Dim rngCell As Range
    Dim strFirstAddress As String
    With wks.Range("D:D")
        Me.ListBox1.Clear
        ListBox1.Tag = 1
        Set rngSource = Range("A1").CurrentRegion
        Set rngSource = rngSource.Offset(1, 0).Resize(rngSource.Rows.Count - 1, rngSource. _
Columns.Count)
        intColums = rngSource.Columns.Count
        Set rngCell = .Find(Me.TextBox1.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not rngCell Is Nothing Then
            strFirstAddress = rngCell.Address
        Do
            With Me.ListBox1
                .ListStyle = fmListStyleOption ' Auswahlfeld zu Beginn der Zeile
                .ColumnCount = intColums
                '.ColumnWidths = ("2,0cm;1,5cm;1,0cm;2,0cm;2,0cm;1,5cm;1,5cm;1,0cm;2,5cm")
                .ColumnHeads = True
                .AddItem
                .List(.ListCount - 1, 0) = rngCell.Offset(0, -3).Value '1. Spalte
                .List(.ListCount - 1, 1) = rngCell.Offset(0, -2).Value '2. Spalte
                .List(.ListCount - 1, 2) = Format(rngCell.Offset(0, -1).Value, "hh:mm") '3. _
Spalte
                .List(.ListCount - 1, 3) = rngCell.Value 'Suchspalte | 4. Spalte
                .List(.ListCount - 1, 4) = rngCell.Offset(0, 1).Value '5. Spalte
                .List(.ListCount - 1, 5) = rngCell.Offset(0, 2).Value '6. Spalte
                .List(.ListCount - 1, 6) = rngCell.Offset(0, 3).Value '7. Spalte
                .List(.ListCount - 1, 7) = Format(rngCell.Offset(0, 4).Value, "#,##0.00 EUR") '  _
8. Spalte
                .List(.ListCount - 1, 8) = rngCell.Offset(0, 5).Value '9. Spalte
            End With
        Set rngSource = Nothing
        ListBox1.Tag = ""
        Set rngCell = .FindNext(rngCell)
        Loop While Not rngCell Is Nothing And rngCell.Address <> strFirstAddress
        Else
            MsgBox "ID nicht gefunden", 48
        End If
    End With
    Application.DisplayAlerts = False
        Workbooks("TEST_DATEN.xlsx").Close SaveChanges:=False
    Application.DisplayAlerts = True
End Sub

Private Sub CommandButton1_Click()
    AutoListBox
End Sub

Private Sub CommandButton2_Click()
    With Me.ListBox1
        UserForm1.TextBox1 = .List(.ListCount - 1, 3) 'Suchspalte | 4. Spalte
        UserForm1.TextBox2 = .List(.ListIndex, 4) '5. Spalte
        UserForm1.ComboBox1 = .List(.ListIndex, 5) '6. Spalte
        UserForm1.ComboBox2 = .List(.ListIndex, 6) '7. Spalte
        UserForm1.TextBox3 = .List(.ListIndex, 7) '8. Spalte
        UserForm1.TextBox4 = .List(.ListIndex, 8) '9. Spalte
    End With
    Unload Me
End Sub


Bild

Betrifft: AW: Datensatz auslesen unter Nutzung einer ListBox
von: Dirk Röseler
Geschrieben am: 28.04.2015 09:23:14
Sorry doppelt ... ist aber auch nicht einfach, sich hier schnell zurecht zu finden ;)

Bild

Betrifft: AW: Datensatz auslesen unter Nutzung einer ListBox
von: Nepumuk
Geschrieben am: 28.04.2015 09:42:46
Hallo,
teste mal:

Private Sub AutoListBox()
    
    Dim lngColums As Long
    Dim rngCell As Range
    Dim strFirstAddress As String
    Dim astrValues() As String, ialngIndex As Long
    Dim objWorkbook As Workbook
    
    Set objWorkbook = Workbooks.Open(Filename:= _
        "C:\Users\Dirk\Documents\TEST_UserForm\TEST_DATEN.xlsx", ReadOnly:=True)
    
    lngColums = Range("A1").CurrentRegion.Columns.Count
    
    With objWorkbook.Worksheets("DATEN").Range("D:D")
        
        With ListBox1
            .Clear
            .Tag = 1
            .ListStyle = fmListStyleOption ' Auswahlfeld zu Beginn der Zeile
            .ColumnCount = lngColums
        End With
        
        Set rngCell = .Find(TextBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)
        
        If Not rngCell Is Nothing Then
            
            strFirstAddress = rngCell.Address
            ListBox1.Tag = ""
            
            Do
                ialngIndex = ialngIndex + 1
                Redim Preserve astrValues(1 To lngColums, 1 To ialngIndex)
                
                astrValues(1, ialngIndex) = rngCell.Offset(0, -3).Value '1. Spalte
                astrValues(2, ialngIndex) = rngCell.Offset(0, -2).Value '2. Spalte
                astrValues(3, ialngIndex) = Format(rngCell.Offset(0, -1).Value, "hh:mm") '3.Spalte
                astrValues(4, ialngIndex) = rngCell.Value 'Suchspalte | 4. Spalte
                astrValues(5, ialngIndex) = rngCell.Offset(0, 1).Value '5. Spalte
                astrValues(6, ialngIndex) = rngCell.Offset(0, 2).Value '6. Spalte
                astrValues(7, ialngIndex) = rngCell.Offset(0, 3).Value '7. Spalte
                astrValues(8, ialngIndex) = Format(rngCell.Offset(0, 4).Value, "#,##0.00 EUR") '8. Spalte
                astrValues(9, ialngIndex) = rngCell.Offset(0, 5).Value '9. Spalte
                
                Set rngCell = .FindNext(rngCell)
                
            Loop Until rngCell.Address = strFirstAddress
        Else
            MsgBox "ID nicht gefunden", vbExclamation
        End If
    End With
    
    ListBox1.Column = astrValues
    
    objWorkbook.Close SaveChanges:=False
    
End Sub

Gruß
Nepumuk

Bild

Betrifft: AW: Datensatz auslesen unter Nutzung einer ListBox
von: Dirk Röseler
Geschrieben am: 28.04.2015 10:13:05
Hallo Nepumuk,
danke für die schnelle Antwort. Klappt super; du bis mein Held. Und übergeben tue ich die Werte zurück in die Userform1 wie? :)
VG Dirk

Bild

Betrifft: AW: Datensatz auslesen unter Nutzung einer ListBox
von: Nepumuk
Geschrieben am: 28.04.2015 10:16:36
Hallo,
was wie? Versteh ich nicht.
Gruß
Nepumuk

Bild

Betrifft: AW: Datensatz auslesen unter Nutzung einer ListBox
von: Dirk Röseler
Geschrieben am: 28.04.2015 10:21:56
Ich meine damit den zweiten Teil. Wenn ich nun eine Zeile aus der ListBox markiere und auf Datensatz übernehmen klicke, sollen diese Daten in die UserForm übergeben werden, wo auch die Erfassung der Datensätze erfolgt:

Private Sub CommandButton2_Click()
    With Me.ListBox1
        UserForm1.TextBox1 = .List(.ListCount - 1, 3) 'Suchspalte | 4. Spalte
        UserForm1.TextBox2 = .List(.ListIndex, 4) '5. Spalte
        UserForm1.ComboBox1 = .List(.ListIndex, 5) '6. Spalte
        UserForm1.ComboBox2 = .List(.ListIndex, 6) '7. Spalte
        UserForm1.TextBox3 = .List(.ListIndex, 7) '8. Spalte
        UserForm1.TextBox4 = .List(.ListIndex, 8) '9. Spalte
    End With
    Unload Me
End Sub
VG Dirk
BTW: Bei der Zeile "ListBox1.Column = astrValues" muss ich glaube ich eine weitere Abfrage hinzufügen, da er mir ansonsten mit einem Laufzeitfehler aussteigt, wenn er in der Suche meinen in der TextBox eingegebenen Wert nicht findet.

Bild

Betrifft: AW: Datensatz auslesen unter Nutzung einer ListBox
von: Nepumuk
Geschrieben am: 28.04.2015 10:29:18
Hallo,
1. Daran hat sich doch nichts geändert. Oder was funktioniert da nicht mehr?
2. Da musst du einfach die Zeile in die Abfrage setzen:

                Set rngCell = .FindNext(rngCell)
                
            Loop Until rngCell.Address = strFirstAddress
            
            ListBox1.Column = astrValues 'neue Position ****************
            
        Else
            MsgBox "ID nicht gefunden", vbExclamation
        End If
    End With
    
    objWorkbook.Close SaveChanges:=False
    
End Sub

Gruß
Nepumuk

Bild

Betrifft: AW: Datensatz auslesen unter Nutzung einer ListBox
von: Dirk Röseler
Geschrieben am: 28.04.2015 10:51:26
Hallo Nepumuk,
Danke nochmals . Das mit der neuen Position habe ich dann doch noch selbst hinbekommen (bin doch nicht zu unfähig ^^). Das mit der "= .List ..." habe ich noch nicht probiert; dachte eben, das es nicht klappen würde, da ich oben keine ".List ..." genutzt habe. Teste ich Heute Abend mal - jetzt erst mal zur Arbeit flitzen (Zeit verpennt bei so etwas ^^).
VG Dirk

Bild

Betrifft: AW: Datensatz auslesen unter Nutzung einer ListBox
von: Dirk Röseler
Geschrieben am: 28.04.2015 21:02:29
So, nun nochmals einen grooooßen Dank an Nepumuk für die Hilfe. Es funktioniert tadellos; habe noch etwas Anpassung vorgenommen (nun mit zwei Suchkriterien) und klappt spitze. Finde Arrays jetzt doch nicht mehr so schwer (bin also doch ein helles Köpfchen)... ;)
Danke Danke Danke
VG Dirk

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Datensatz auslesen unter Nutzung einer ListBox"