Duplikate und Leerzeilen ComboBox

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

Betrifft: Duplikate und Leerzeilen ComboBox
von: Marlen
Geschrieben am: 12.10.2015 11:15:13

Hallo liebe Profis,
und weiter geht's mit meinen VBA-Problemen. Als erstes die Datei:
https://www.herber.de/bbs/user/100720.xls
Nun zum Vorgehen.
Mit Klick auf die Schaltfläche öffnet sich das Userform. Darin enthalten sind 3 Comboboxen und eine TextBox. Combo1 sind Tarife, Combo 2 Leistung. In Tabelle 2 stehen die unterschiedlichen Kombinationsmöglichkeiten. Jetzt wollte ich folgendes darstellen:
a) Beide Comboboxen ohne Duplikate. Es soll also nur einmal NO usw. dargestellt werden.
b) Wenn ich z.B. NO und 0-60 kombiniere soll in der TextBox NO_60 angezeigt werden. Also der jeweilig zugehörige Tarif.
Ich hatte im Archiv schon ein bisschen gestöbert und einige Varianten gefunden. Leider bin ich zu blond um sie für mich anzupassen. Könnt ihr mir da mit einem Code helfen?
Das nächste Problem ist meine Combo3. Hier sind Kunden aufgelistet mit Leezeilen dazwischen. In der Combo sollen aber nur Einträge die <> "" sind angezeigt werden. Wie geht das?
Vielen lieben Dank.
Marlen

Bild

Betrifft: AW: Duplikate und Leerzeilen ComboBox
von: Nepumuk
Geschrieben am: 12.10.2015 12:06:50
Hallo,
und was wenn ich als 60-x oder kom auswähle?
Hier mal ein Beispiel wie du die Leerzeilen und Duplikate raus bekommst:

Private Sub UserForm_Initialize()
    Dim avntValues As Variant
    Dim lngIndex As Long, ialngRow As Long
    Dim objDictionary As Object
    Set objDictionary = CreateObject("Scripting.Dictionary")
    With Worksheets("Tabelle2")
        For lngIndex = 1 To 3
            avntValues = .Range(.Cells(2, lngIndex), .Cells(.Rows.Count, lngIndex)).Value
            For ialngRow = LBound(avntValues) To UBound(avntValues)
                If Not IsEmpty(avntValues(ialngRow, 1)) Then _
                    objDictionary(avntValues(ialngRow, 1)) = vbNullString
            Next
            Controls("ComboBox" & CStr(lngIndex)).List = objDictionary.Keys
            objDictionary.RemoveAll
        Next
    End With
    Set objDictionary = Nothing
End Sub

Gruß
Nepumuk

Bild

Betrifft: AW: Duplikate und Leerzeilen ComboBox
von: Marlen
Geschrieben am: 12.10.2015 12:10:25
Hallo Nepumuk,
den Code probiere ich dann gleich aus. Wenn du 60-x auswählst soll NO_x raus kommen und bei kom NO_kom usw. Im Endeffekt sind die Kombinationsmöglichkeiten in der Tabelle schon passend dargestellt. Also auch wenn ich unter BZ und "" auswähle soll BZ_A raus kommen. In der Spalte muss auch die Leerzeile angezeigt werden.
Gruß
Marlen

Bild

Betrifft: Nicht überall alle Leerzeilen raus
von: Marlen
Geschrieben am: 12.10.2015 12:14:59
Jetzt habe ich den Code getestet. Leider passiert noch nicht so ganz das, was ich mir gedacht habe.
Könntest du mir vielleicht das vorgehen auskommentieren? Ich bin kein VBA-Pro und kann deshalb den Code nicht wirklich nachvollziehen. Was bewirkt dabei das die Duplikate raus sind und was die Leerzeilen?

Bild

Betrifft: AW: Nicht überall alle Leerzeilen raus
von: Marlen
Geschrieben am: 12.10.2015 13:50:48
Hat vielleicht noch wer eine Idee?

Bild

Betrifft: AW: Nicht überall alle Leerzeilen raus
von: Nepumuk
Geschrieben am: 12.10.2015 15:27:51
Hallo,
schau mal:

Private Sub UserForm_Initialize()
    Dim avntValues As Variant
    Dim lngIndex As Long, ialngRow As Long
    Dim objDictionary As Object
    'Instanz eine Dictionary-Objektes erstellen
    Set objDictionary = CreateObject("Scripting.Dictionary")
    'Bezug zur Tabelle 2
    With Worksheets("Tabelle2")
        'Schleife für 3 Spalten in 3 ComboBoxen
        For lngIndex = 1 To 3
            'Daten der Spalten nacheinander in ein Array einlesen
            avntValues = .Range(.Cells(2, lngIndex), .Cells(.Rows.Count, lngIndex)).Value
            'Schleife über alle Einträge im Array
            For ialngRow = LBound(avntValues) To UBound(avntValues)
                'Wenn die Zelle nicht Leer ist dann Key im Dictionamry erstellen
                If Not IsEmpty(avntValues(ialngRow, 1)) Then _
                    objDictionary(avntValues(ialngRow, 1)) = vbNullString
            Next
            'Der entsprechenden ComboBox die Keys übergeben
            Controls("ComboBox" & CStr(lngIndex)).List = objDictionary.Keys
            'Dictionary für den nächsten Durchlauf leeren
            objDictionary.RemoveAll
        Next
    End With
    'Dictionary aus den Speicher entfernen
    Set objDictionary = Nothing
End Sub

Ein Dictionary ist ein Schlüssel-Wert Array. Damit: objDictionary(Key) = Value wird ein neuer Schlüssel mit einem Wert erzeugt. Da die Schlüssel eindeutig sind wird beim wiederholten Zuweisen eines Wertes nur der alte Wert überschrieben. Die Werte sind ein leerer String denn uns interessieren nur die Schlüssel.
Gruß
Nepumuk

Bild

Betrifft: AW: Nicht überall alle Leerzeilen raus
von: Marlen
Geschrieben am: 12.10.2015 15:41:14
Puh irgendwie verstehe ich nur Bahnhof.
Erst einmal danke für die Mühe. Leider ergibt der Code auch nicht das, was ich gern haben möchte. Im Endeffekt muss in der Combobox Leistung angezeigt werden:
- 0-60
- 60 - x
- Kom
- (eine leere Zeile)
In der Box Gebiet nur die Gebiete (das macht der Code).
Außerdem weiß ich leider nicht, wie ich die 2 Werte, die ich dann in Gebiet und Leistung ausgewählt habe so kombiniere, dass mir im Tarif der entsprechende Wert angezeigt wird.
Nur in der Combobox mit dem Kunden sollen die Leerzeilen raus sein.
Vielleicht sollte ich das Projekt doch an den Nagel hängen. Ich wird leider so gar nicht schlau draus.

Bild

Betrifft: AW: Nicht überall alle Leerzeilen raus
von: Nepumuk
Geschrieben am: 12.10.2015 16:27:36
Hallo,
eine Leerzeile unten anfügen kannst du ganz einfach so:

End With
'Leerzeile einfügen
ComboBox2.AddItem vbNullString

Die TextBox füllst du so:
Private Sub ComboBox1_Change()
    With ComboBox2
        TextBox1.Value = ComboBox1.Value & IIf(.Value = vbNullString, vbNullString, " - ") & .Value
    End With
End Sub

Private Sub ComboBox2_Change()
    With ComboBox2
        TextBox1.Value = ComboBox1.Value & IIf(.Value = vbNullString, vbNullString, " - ") & .Value
    End With
End Sub

Gruß
Nepumuk

Bild

Betrifft: Laufzeitfehler
von: Marlen
Geschrieben am: 13.10.2015 07:24:50
Guten Morgen,
ich hab mich heute Morgen noch einmal an den Code für das Dictionary gesetzt. Da bekomme ich den Fehler:
"Laufzeitfehler '-2147024809 (80070057)':
Das angegebene Objekt konnte nicht gefunden werden."
Er bezieht sich im Debugger dabei auf: Controls("ComboBox1" & CStr(lngIndex)).List = objDictionary.Keys
Woran liegt das denn

Bild

Betrifft: AW: Laufzeitfehler
von: Nepumuk
Geschrieben am: 13.10.2015 07:28:05
Hallo,
nicht ComboBox1 sondern nur ComboBox.
Gruß
Nepumuk

Bild

Betrifft: AW: Laufzeitfehler
von: Marlen
Geschrieben am: 13.10.2015 07:34:27
Auch da bringt er mir den Fehler. Ich vermute es liegt wohl daran, dass die Comboboxen in der Originaldatei mit Gebiet, Leistung und Kunde bezeichnet sind oder?

Bild

Betrifft: AW: Laufzeitfehler
von: Nepumuk
Geschrieben am: 13.10.2015 07:43:43
Hallo,
das Umbenennen von Controls in einem Userform bring nichts außer einen Mehrtaufwand beim programmieren. Der Benutzer sieht es nicht und du solltest wissen wofür welches Control steht.
Gruß
Nepumuk

Bild

Betrifft: befüllen der Textbox
von: Marlen
Geschrieben am: 13.10.2015 07:46:02
Nachdem ich alle umbenannt habe, laufen zumindest die Comboboxen. Jetzt fehlt mir nur noch die TextBox. Mit dem Code füllt er leider nicht so, wie er sollte. Er kombiniert einfach die Auswahl aus Combobox 1 und 2. Er soll aber als Beispiel:
Ich wähle in Combo 1 NO aus, in Combo 2 0-60, dann soll er in der Tabelle 2 die gleiche Kombination suchen und den Tarif aus Spalte C ausgeben.
Vielen Dank schon mal.

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Duplikate und Leerzeilen ComboBox"