ColumnHeads in ListBox (UF) anzeigen
13.05.2008 14:41:02
Josef
Mit folgfendem Code werden die Werte über eine ComboBox in die ListBox eingelesen.
Wo und wie müßte man bitte den Code so ergänzen oder verändern. dass beim Changeereignis in der Combobox die Daten in die ListBox mit ColumnHeads eingelesen werden?
Danke
Josef
Private Sub subNamen5_Change()
On Error Resume Next
Dim aRow As Long
Dim iRow As Long
Dim wks As Worksheet
Set wks = Workbooks("Masterfile.xls").Worksheets("SUBKAPITEL")
Dim lCoBo As Long
'subNamen5.Text = subNamen5.Column(0) & " " & subNamen5.Column(1)
'Dim col As New Collection
With ListBox7
.Clear ' löschen ComboBox
.ColumnCount = 3 ' drei Spalten
.ColumnWidths = "2,5 cm;1,5 cm;10 cm" ' Breite der Spalten
' .ListRows = 12 ' angezeigte Zeilen
'.Height = 20 ' Höhe der ComboBox
'.Font.Size = 8 ' Schriftgröße
' .BackColor = RGB(204, 255, 204) ' Hintergrundfarbe
'.Width = 417.75
' .ColumnHeads = True
End With
aRow = IIf(IsEmpty(wks.Range("A65536")), wks.Range("A65536").End(xlUp).Row, 65536)
For iRow = 2 To aRow
'With wks
If wks.Cells(iRow, 4) = subNamen6 _
And wks.Cells(iRow, 7) = subNamen5 Then
If Err = 0 And _
wks.Cells(iRow, 7) = _
subNamen5.Value Then
ListBox7.AddItem ""
ListBox7.List(lCoBo, 0) = _
wks.Cells(iRow, 8)
ListBox7.List(lCoBo, 1) = _
wks.Cells(iRow, 9)
ListBox7.List(lCoBo, 2) = _
wks.Cells(iRow, 10)
'subNamen3.List(lCoBo, 2) = _
' wks.Cells(iRow, 7)
'subNamen3.List(lCoBo, 3) = _
' wks.Cells(iRow, 5)
lCoBo = lCoBo + 1
Else
Err.Clear
End If
End If
'End With
Next iRow
Label170.Caption = "Subkapitel lfd.Nr"
Label171.Caption = "Subkapitel Nr"
Label172.Caption = "Subkapitel Name"
On Error GoTo 0
End Sub
Private Sub subNamen5_Enter()
Dim aRow, iRow As Long
Dim wks As Worksheet
' Dim lCoBo As Long
Set wks = Workbooks("Masterfile.xls").Worksheets("SUBKAPITEL")
Dim col As New Collection
subNamen5.Clear
With ListBox7
.Width = 417.75
' .Clear ' löschen ComboBox
' .ColumnCount = 2 ' drei Spalten
' .ColumnWidths = "1,5 cm;10 cm" ' Breite der Spalten
' .ListRows = 12 ' angezeigte Zeilen
'.Height = 20 ' Höhe der ComboBox
'.Font.Size = 8 ' Schriftgröße
' .BackColor = RGB(204, 255, 204) ' Hintergrundfarbe
End With
Label176.Visible = False
Label177.Visible = False
Label178.Visible = False
aRow = IIf(IsEmpty(wks.Range("A65536")), wks.Range("A65536").End(xlUp).Row, 65536)
On Error Resume Next
For iRow = 2 To aRow
'With wks
If wks.Cells(iRow, 4) = subNamen6 Then
col.Add wks.Cells(iRow, 1), wks.Cells(iRow, 7)
If Err = 0 Then
With Me.subNamen5
.AddItem wks.Cells(iRow, 6)
.List(.ListCount - 1, 1) = wks.Cells(iRow, 7)
End With
Else
Err.Clear
End If
End If
'End With
Next iRow
On Error GoTo 0
'Call Sortieren_CboN5
End Sub