AW: Combobox
03.08.2022 16:53:12
Nepumuk
Hallo Marc,
zum Weiterentwickeln:
Option Explicit
Private Sub ComboBox1_Change()
If ComboBox1.ListIndex > -1 Then
With Tabelle1
If .FilterMode Then Call .ShowAllData
End With
Call ComboBox2.Clear
Call ComboBox3.Clear
Call Tabelle1.AutoFilter.Range.AutoFilter(Field:=1, Criteria1:=ComboBox1.Text)
Call FillBox(2)
End If
End Sub
Private Sub ComboBox2_Change()
If ComboBox2.ListIndex > -1 Then
Call ComboBox3.Clear
Call Tabelle1.AutoFilter.Range.AutoFilter(Field:=2, Criteria1:=ComboBox2.Text)
Call FillBox(3)
End If
End Sub
Private Sub ComboBox3_Change()
If ComboBox3.ListIndex > -1 Then
Call Tabelle1.AutoFilter.Range.AutoFilter(Field:=3, Criteria1:=ComboBox3.Text)
End If
End Sub
Private Sub UserForm_Initialize()
With Tabelle1
If .FilterMode Then Call .ShowAllData
End With
Call FillBox(1)
End Sub
Private Sub FillBox(ByVal pvlngColumn As Long)
Dim objDataObject As DataObject
Dim objDictionary As Object
Dim strText As String
Dim avntValues As Variant, vntItem As Variant
Set objDictionary = CreateObject(Class:="Scripting.Dictionary")
Set objDataObject = New DataObject
With Tabelle1.AutoFilter.Range
Call Range(.Cells(2, pvlngColumn), .Cells(.Rows.Count, pvlngColumn)).Copy
End With
With objDataObject
.GetFromClipboard
strText = .GetText
End With
Set objDataObject = Nothing
Application.CutCopyMode = False
strText = Left$(strText, Len(strText) - 2)
avntValues = Split(strText, vbCrLf)
For Each vntItem In avntValues
objDictionary.Item(vntItem) = vbNullString
Next
Controls("ComboBox" & CStr(pvlngColumn)).List = objDictionary.Keys
Set objDictionary = Nothing
End Sub
Gruß
Nepumuk