AW: Abhängige Comboboxen alphabetisch sortiert
30.10.2015 18:29:14
Nepumuk
Hallo,
nachdem ich deine Frage nochmal gelesen hatte, fiel mir auf dass ich die Abhängigkeit vergessen habe. Also:
Option Explicit
Dim dicTarif As Object
Private Sub ComboBox1_Change()
If ComboBox1.ListIndex > -1 Then _
ComboBox3.List = GetSortedContent(Worksheets("Tabelle2"), 2, ComboBox1.Text)
End Sub
Private Sub ComboBox1_Click()
TextBox1.Text = dicTarif(ComboBox1.Text & "|" & ComboBox2.Text)
End Sub
Private Sub ComboBox2_Click()
TextBox1.Text = dicTarif(ComboBox1.Text & "|" & ComboBox2.Text)
End Sub
Private Sub UserForm_Initialize()
Dim dicGebiet As Object
Dim dicLeistung As Object
Dim arr
Dim z As Long
Set dicGebiet = CreateObject("scripting.dictionary")
Set dicLeistung = CreateObject("scripting.dictionary")
Set dicTarif = CreateObject("scripting.dictionary")
arr = Sheets("Tabelle2").Cells(1, 1).CurrentRegion.Value
For z = 2 To UBound(arr, 1)
dicGebiet(arr(z, 1)) = 0
dicLeistung(arr(z, 2)) = 0
dicTarif(arr(z, 1) & "|" & arr(z, 2)) = arr(z, 3)
Next
ComboBox1.List = dicGebiet.Keys
ComboBox2.List = dicLeistung.Keys
End Sub
Private Function GetSortedContent(ByRef probjWorksheet As Worksheet, _
ByVal pvlngStartRow As Long, ByVal pvstrValue As String) As Variant
Dim objSortedList As Object, objArrayList As Object
Dim lngIndex As Long
Dim vntArray As Variant
Set objSortedList = CreateObject(Class:="System.Collections.SortedList")
Set objArrayList = CreateObject(Class:="System.Collections.ArrayList")
With probjWorksheet
vntArray = .Range(.Cells(pvlngStartRow, 1), .Cells( _
.Rows.Count, 4).End(xlUp)).Value2
End With
For lngIndex = 1 To UBound(vntArray)
If vntArray(lngIndex, 1) = pvstrValue Then _
objSortedList(vntArray(lngIndex, 4)) = vbNullString
Next
Call objArrayList.AddRange(objSortedList.Keys)
GetSortedContent = objArrayList.ToArray
Set objSortedList = Nothing
Set objArrayList = Nothing
End Function
Gruß
Nepumuk