AW: Auflisten von Daten in Listbox
16.08.2010 15:44:13
Daten
Hallo Urmilla,
hier der entsprechende Code für das Userform-Modul.
Gruß
Franz
Option Explicit
Private wksData As Worksheet, ZeileTabelle As Long
Private Const Zeile_1_Data As Long = 2 '1. Datenzeile in Liste
Private Sub Update_Listbox1()
Dim Zeile As Long, ZeileLetzte As Long
Dim oCollection As New Collection
On Error GoTo Fehler
Me.ListBox1.Clear
With wksData
ZeileLetzte = .Cells(.Rows.Count, 1).End(xlUp).Row
For Zeile = Zeile_1_Data To ZeileLetzte
If .Cells(Zeile, 1) "" Then
oCollection.Add .Cells(Zeile, 1), .Cells(Zeile, 1).Text
With Me.ListBox1
.AddItem wksData.Cells(Zeile, 1)
.List(.ListCount - 1, 1) = wksData.Cells(Zeile, 2).Text
.List(.ListCount - 1, 2) = wksData.Cells(Zeile, 3)
End With
Resume01:
End If
Next
End With
ZeileTabelle = 0
Fehler:
With Err
Select Case .Number
Case 0 'Alles ok
Case 457 'Doppelter Key in Collection
Resume Resume01
Case Else
MsgBox "Fehler-Nr.: " & .Number & vbNewLine & .Description
End Select
End With
End Sub
Private Sub Update_Listbox2()
Dim Zeile As Long, ZeileLetzte As Long, vProjekt
On Error GoTo Fehler
Me.ListBox2.Clear
With Me.ListBox1
vProjekt = .Value
End With
With wksData
ZeileLetzte = .Cells(.Rows.Count, 1).End(xlUp).Row
For Zeile = Zeile_1_Data To ZeileLetzte
If .Cells(Zeile, 1) = vProjekt Then
With Me.ListBox2
.AddItem wksData.Cells(Zeile, 1)
.List(.ListCount - 1, 1) = wksData.Cells(Zeile, 2).Text
.List(.ListCount - 1, 2) = wksData.Cells(Zeile, 3)
.List(.ListCount - 1, 3) = Zeile
End With
End If
Next
End With
ZeileTabelle = 0
Fehler:
With Err
Select Case .Number
Case 0 'Alles ok
Case Else
MsgBox "Fehler-Nr.: " & .Number & vbNewLine & .Description
End Select
End With
End Sub
Private Sub ListBox1_Click()
Call Update_Listbox2
End Sub
Private Sub ListBox2_Click()
'Beispielaktion bei Auswahl eines Eintrags in Listbox2
With Me.ListBox2
'Zeile des gewählten Eintrags in der Tabelle
ZeileTabelle = CLng(.List(.ListIndex, 3))
MsgBox "Zeile des gewählten Eintrags in der Tabelle: " & ZeileTabelle
End With
End Sub
Private Sub UserForm_Initialize()
Set wksData = Worksheets("Tabelle1")
With Me.ListBox1
.ColumnCount = 3
.ColumnWidths = "100pt;60pt;120pt"
End With
With Me.ListBox2
.ColumnCount = 4 'In 4. Spalte wird Zeilennummer in Tabelle gespeichert
.ColumnWidths = "100pt;60pt;120pt;0Pt"
End With
Call Update_Listbox1
End Sub