Abhängige Comboboxen alphabetisch sortiert

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
UserForm ListBox TextBox
Bild

Betrifft: Abhängige Comboboxen alphabetisch sortiert
von: Marlen
Geschrieben am: 30.10.2015 11:26:58

Hallo Profis,
Case hat mir in diesem Beitrag schon gut geholfen meine Combobox alphabetisch zu füllen:
https://www.herber.de/forum/archiv/1452to1456/1454917_Combobox_alphabetisch_fuellen.html
Ich habe die Datei noch ein wenig ergänzt.
https://www.herber.de/bbs/user/101121.xls
Die beiden Comboboxen 1 und 2 füllen meine TextBox in Abhängigkeit. Jetzt soll in der Combobox 3 in Abhängigkeit die Kunden angezeigt werden, die zum jeweiligen Gebiet gehören. Also Kunde A und K zum Gebiet NO und die noch alphabetisch sortiert. Habe das versucht in der Zuordnung von Combobox 1 und 2 zu ergänzen, bin aber kläglich gescheitert.
Könnt ihr bitte helfen? Danke.

Bild

Betrifft: AW: Abhängige Comboboxen alphabetisch sortiert
von: Nepumuk
Geschrieben am: 30.10.2015 18:09:43
Hallo,
so?

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
    
    ComboBox3.List = GetSortedContent(Worksheets("Tabelle2"), 4, 2)
    
End Sub

Private Function GetSortedContent(ByRef probjWorksheet As Worksheet, _
        ByVal pvlngColumn As Long, ByVal pvlngStartRow As Long) 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, pvlngColumn), .Cells( _
            .Rows.Count, pvlngColumn).End(xlUp)).Value2
    End With
    
    For lngIndex = 1 To UBound(vntArray)
        objSortedList(vntArray(lngIndex, 1)) = vbNullString
    Next
    
    Call objArrayList.AddRange(objSortedList.Keys)
    
    GetSortedContent = objArrayList.ToArray
    
    Set objSortedList = Nothing
    Set objArrayList = Nothing
    
End Function

Gruß
Nepumuk

Bild

Betrifft: AW: Abhängige Comboboxen alphabetisch sortiert
von: Nepumuk
Geschrieben am: 30.10.2015 18:29:14
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

Bild

Betrifft: AW: Abhängige Comboboxen alphabetisch sortiert
von: Marlen
Geschrieben am: 02.11.2015 07:47:30
Hallo Nepumuk,
vielen Dank für deine Antwort. Habe es jetzt probiert. Der Code bricht bei:
With probjWorksheet
vntArray = .Range(.Cells(pvlngStartRow, pvlngColumn), .Cells( _
.Rows.Count, pvlngColumn).End(xlUp)).Value2
ab, und meckert mir an: "Ein Objekt erforderlich".
Da ich leider den Code selbst nicht ganz durchdenken kann, weiß ich leider nicht woran es liegt.

Bild

Betrifft: Einfache Sortiermethode für kleiner Datenmengen
von: Daniel
Geschrieben am: 02.11.2015 09:06:38
Hi Marlen
probier mal folgenden Code:

Dim dicTarif As Object
Private Sub ComboBox1_Click()
    TextBox1.Text = dicTarif(ComboBox1.Text & "|" & ComboBox2.Text)
    Call WerteFürCombobox3Sortiert
End Sub
Private Sub ComboBox2_Click()
    TextBox1.Text = dicTarif(ComboBox1.Text & "|" & ComboBox2.Text)
    Call WerteFürCombobox3Sortiert
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
a = Sheets("Tabelle2").Cells(Rows.Count, 1).End(xlUp).Row
Call WerteFürCombobox3Sortiert
End Sub
Private Sub WerteFürCombobox3Sortiert()
Dim zL As Long
Dim zC As Long
Dim GesamtListe
Dim Gebiet As String
Dim Leistung As String
GesamtListe = Sheets("Tabelle2").Cells(1, 1).CurrentRegion.Value
Gebiet = ComboBox1.Text
Leistung = ComboBox2.Text
With ComboBox3
    .Clear
    For zL = 2 To UBound(GesamtListe, 1)
        If GesamtListe(zL, 1) = Gebiet Or Gebiet = "" Then 
            If GesamtListe(zL, 2) = Leistung Or Leistung = "" Then
                For zC = 0 To .ListCount - 1
                    If UCase(GesamtListe(zL, 4)) < UCase(.List(zC)) Then Exit For
                Next
                .AddItem GesamtListe(zL, 4), zC
            End if
        End If
    Next
End With
            
End Sub
die Methode, um eine sortierte Listbox zu erstellen, ist nicht besonders schnell, dafür aber sehr einfach und sollte daher auch mit geringen VBA-Kenntnissen Verständlich sein.
Beim Befüllen der Listbox wird diese, wenn bereits Werte vorhanden sind, diese in einer Schleife durchlaufen und geprüft, ob der einzufügende Wert kleiner ist, als der aktuelle Wert aus der Liste und wenn ja, wird er vor diesem Wert eingefügt.
Gruß Daniel

Bild

Betrifft: AW: Einfache Sortiermethode ....
von: Marlen
Geschrieben am: 04.11.2015 11:52:13
Hallo Daniel,
in meiner Testdatei funktioniert das alles prima. Jetzt habe ich es versucht in das eigentliche Original zu übernehmen. Da bringt er mir jetzt Laufzeitfehler 13 Typenunverträglichkeit. Leider markert er mir auch nicht an, wo er Probleme hat. Das Userform wird genau wie in der Testdatei aufgerufen, nur das ich im Arr nur das Gebiet abfrage. Die Leistung spielt keine Rolle. Habt ihr noch eine Idee, wo ich da was falsch mache?

Bild

Betrifft: AW: Einfache Sortiermethode - Ergänzung
von: Marlen
Geschrieben am: 04.11.2015 11:55:11
Wenn ich statt userform_initialize () userform activate () Kommt der Fehler nicht, dafür meckert er mir dann das For z= 2 to UBound (arr,1) an.

Bild

Betrifft: AW: Einfache Sortiermethode - Ergänzung
von: daniel
Geschrieben am: 04.11.2015 13:12:47
dann solltest du dir mal anschauen, wie arr erzeugt wird.
wahrscheinlich über arr = Range(...).Value
wenn der zellbereich bei Range(...) aus nur einer Zelle besteht, wird bei dieser Zuweisung das arr kein Array, sondern nur eine einfache Wertvariable.
Gruß Daniel

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Abhängige Comboboxen alphabetisch sortiert"