AW: Listbox Spalten clearen
21.11.2023 09:59:07
Alwin Weisangler
Hallo Reaper,
anbei mal ein Übergabeweg in die Listbox, wie ich es machen würde.
Option Explicit
Dim arrLB()
Private Sub ComboBox1_Change()
Dim sortList As Object, arrList As Object
Dim arr(), arrTmp, i&
Set sortList = CreateObject("System.Collections.SortedList")
Set arrList = CreateObject("System.Collections.ArrayList")
arr = Tabelle1.ListObjects(1).DataBodyRange.Columns(ComboBox1.ListIndex + 2).Value
For i = 1 To UBound(arr)
If arr(i, 1) > "" Then
sortList(arr(i, 1)) = ""
End If
Next i
arrList.AddRange sortList.keys
With ComboBox2
.Clear
.AddItem "-- Alles --"
For i = 0 To sortList.keys.Count - 1
.AddItem arrList(i)
Next i
End With
If ComboBox2.ListIndex = -1 Then ListBox1.Clear
End Sub
Private Sub ComboBox2_Change()
Dim i&, j&, iLevel&, rngLevel As Range
With Tabelle1
If ComboBox2 = "-- Alles --" Then
ReDim arrLB(1 To .ListObjects(1).DataBodyRange.Rows.Count, 1 To 4)
For i = 1 To UBound(arrLB)
arrLB(i, 1) = .ListObjects(1).DataBodyRange.Cells(i, 1)
arrLB(i, 2) = .ListObjects(1).DataBodyRange.Cells(i, ComboBox1.ListIndex + 2)
arrLB(i, 3) = .ListObjects(2).DataBodyRange.Cells(i, 1)
arrLB(i, 4) = .ListObjects(2).DataBodyRange.Cells(i, ComboBox1.ListIndex + 2)
Next i
Else
If ComboBox2.ListIndex = -1 Then
ListBox1.Clear
Exit Sub
End If
For i = 1 To .ListObjects(1).DataBodyRange.Rows.Count
If ComboBox2 = .ListObjects(1).DataBodyRange.Cells(i, ComboBox1.ListIndex + 2).Text Then j = j + 1
Next i
ReDim arrLB(1 To j, 1 To 4)
j = 0
For i = 1 To .ListObjects(1).DataBodyRange.Rows.Count
If ComboBox2 = .ListObjects(1).DataBodyRange.Cells(i, ComboBox1.ListIndex + 2).Text Then
j = j + 1
arrLB(j, 1) = .ListObjects(1).DataBodyRange.Cells(i, 1)
arrLB(j, 2) = .ListObjects(1).DataBodyRange.Cells(i, ComboBox1.ListIndex + 2)
arrLB(j, 3) = .ListObjects(2).DataBodyRange.Cells(i, 1)
arrLB(j, 4) = .ListObjects(2).DataBodyRange.Cells(i, ComboBox1.ListIndex + 2)
End If
Next i
End If
End With
ListBox1.List = arrLB
End Sub
Private Sub UserForm_Initialize()
Dim i&
With Tabelle1.ListObjects(1)
For i = 2 To .DataBodyRange.Columns.Count
ComboBox1.AddItem .HeaderRowRange(i).Value
Next i
End With
End Sub
https://www.herber.de/bbs/user/164472.xlsm
Gruß Uwe