Live-Forum - Die aktuellen Beiträge
Datum
Titel
04.05.2024 20:49:40
Listbox - ausgewählte Zeile aus Tabellenblatt markieren
04.05.2024 16:03:54
Problem mit einer Regel bei bedingter Formatierung
04.05.2024 10:29:31
.Find(what:=..., Lookat:=xlWhole, LookIn:=xlValues) ->For
04.05.2024 09:44:33
Bestimmten bereich aus einem String per VBA separieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1900to1904
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1900to1904
1900to1904
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Combobox mit eindeutigen Werten aus Tab.

Combobox mit eindeutigen Werten aus Tab.
28.09.2022 12:41:48
Thomas
Hallo,
ich möchte eine Combobox mit eindeutigen Werten aus einer Tabelle füllen. Es gibt ein paar If abfragen, ob Optionsfelder gefüllt sind und wenn ja dann sollen die Werte aus Spalte 5 in die Combobox geschrieben werden, da die Werte in Spalte 5 aber mehrmals vorkommen können, soll der jeweilige Wert nur einmal reingeschriben werden. Irgendwie bekomme ich das nicht, es werden immer alle rein geshchreiben oder bei dieser Idee bekomme ich immer den Fehler:
"Funktion oder Variable erwartet" an dieser stelle 

forMitarbeiter.cobStandortAuswahl.AddItem
Der Code sieht hier bis jetzt so aus:

Public Sub StandortFuellen()
Dim lngRow As Long
Dim lngLastRow
With tblDaten
lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
If forMitarbeiter.optWest = True And forMitarbeiter.optFertigung = True Then
For lngRow = 3 To lngLastRow
If .Cells(lngRow, 6) = "West" And .Cells(lngRow, 7) = "Fertigung" Then
If WorksheetFunction.CountIf(Range(.Cells(2, 5), .Cells(lngRow, 5)), .Cells(lngRow, 5)) = 1 Then
forMitarbeiter.cobStandortAuswahl.AddItem = (.Cells(lngRow, 5))
End If
End If
Next lngRow
End With
End Sub
Die Forms befinden sich in einem Userform und der Code in einem Modul deshalb immer die Referenzierung.
Was mache ich bei dem befüllen der Combobox falsch?
Danke

18
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Combobox mit eindeutigen Werten aus Tab.
28.09.2022 12:55:29
ChrisL
Hi Thomas
Mal ohne zu testen...
Punkt vor Range
und bei AddItem das gleich weglassen.

If WorksheetFunction.CountIf(.Range(.Cells(2, 5), .Cells(lngRow, 5)), .Cells(lngRow, 5)) = 1 Then
forMitarbeiter.cobStandortAuswahl.AddItem .Cells(lngRow, 5)
End If
cu
Chris
AW: Combobox mit eindeutigen Werten aus Tab.
28.09.2022 15:47:21
Thomas
Hi Chris,
danke für den Tip, das war zumindest ein Fehler, die Sub läuft nun durch, allerdings ist das Ergebnis nicht ganz richtig....
Ich hätte erwartet wenn "West" und "Fertigung" gefunden wird, das dann "Köln" und "Leverkusen" einmal in der Combobox stehen würden, allerdings steht nur Köln drin....
Userbild
MFG
Anzeige
AW: Combobox mit eindeutigen Werten aus Tab.
28.09.2022 15:57:52
ChrisL
Hi
Bei mir läufts. Allerdings habe ich noch festgestellt, dass ein End-If fehlt.
cu
Chris
AW: Combobox mit eindeutigen Werten aus Tab.
28.09.2022 16:12:54
Thomas
Bei mir nicht, wird immer nur "Köln" angezeigt, bzw. bei den anderen Kombinationen der Options Buttons kommt auch nicht raus was es soll. Im Anhang mal eine Beispieldatei mit dem Userform wo die Options Button ausgewählt werden.
https://www.herber.de/bbs/user/155431.xlsm
AW: Combobox mit eindeutigen Werten aus Tab.
28.09.2022 16:18:19
Daniel
HI
das liegt daran, dass dein CountIf nur die Spalte 5 mit den Städtenamen prüft, ohne die Spalten 6 und 7 zu berücksichtigen.
und wenn "Leverkusen - West - Fertigung" dran ist, dann ist es nicht das erste "Leverkusen" in der Liste, sondern schon das fünfte und kommt damit nicht in die Liste.
wenn dann müsstest du hier mit CountIFs arbeiten und auch Spalte 6 und 7 mit in die Prüfung einbeziehen, oder du prüfst, ob Leverkusen schon in der Listbox drin ist.
ich mache das immer mit einer einfachen schleife (Listboxen sind ja in der Regeln nicht so lang), außerdem kann ich dabei, wenn ich will, die Listbox auch mit wenig aufwand sortiert befüllen.
hier nur mal die Einfache Variante für Duplikatfreie Befüllung in gleicher Reihenfolge.
ist jetzt halt mit Objektschleife und List- statt Combobox, aber ich hoffe du verstehst das Prinzip und kannst es umsetzen.

Dim Zelle As Range
Dim i As Long
Me.ListBox1.Clear
For Each Zelle In Range("A1").CurrentRegion
For i = 0 To ListBox1.ListCount - 1
If Zelle.Value = ListBox1.List(i) Then Exit For
Next
If i = ListBox1.ListCount Then ListBox1.AddItem Zelle.Value
Next
Gruß Daniel
Anzeige
AW: Combobox mit eindeutigen Werten aus Tab.
28.09.2022 17:01:01
Thomas
Hallo Daniel,
danke für den Tip,
ich glaube/hoffe das ich es jetzt habe...

Option Explicit
'Combobox cobStandortAuswahl füllen
Public Sub StandortFuellen()
Dim lngRow As Long
Dim lngLastRow As Long
Dim lngI As Long
With tblDaten
lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
If forMitarbeiter.optWest = True And forMitarbeiter.optFertigung = True Then
forMitarbeiter.cobStandortAuswahl.Clear
For lngRow = 3 To lngLastRow
If .Cells(lngRow, 6) = "West" And .Cells(lngRow, 7) = "Fertigung" Then
For lngI = 0 To forMitarbeiter.cobStandortAuswahl.ListCount - 1
If .Cells(lngRow, 5) = forMitarbeiter.cobStandortAuswahl.List(lngI) Then Exit For
Next lngI
If lngI = forMitarbeiter.cobStandortAuswahl.ListCount Then forMitarbeiter.cobStandortAuswahl.AddItem .Cells(lngRow, 5)
End If
Next lngRow
ElseIf forMitarbeiter.optWest = True And forMitarbeiter.optZentrale = True Then
forMitarbeiter.cobStandortAuswahl.Clear
For lngRow = 3 To lngLastRow
If .Cells(lngRow, 6) = "West" And .Cells(lngRow, 7) = "Zentrale" Then
For lngI = 0 To forMitarbeiter.cobStandortAuswahl.ListCount - 1
If .Cells(lngRow, 5) = forMitarbeiter.cobStandortAuswahl.List(lngI) Then Exit For
Next lngI
If lngI = forMitarbeiter.cobStandortAuswahl.ListCount Then forMitarbeiter.cobStandortAuswahl.AddItem .Cells(lngRow, 5)
End If
Next lngRow
ElseIf forMitarbeiter.optOst = True And forMitarbeiter.optFertigung = True Then
forMitarbeiter.cobStandortAuswahl.Clear
For lngRow = 3 To lngLastRow
If .Cells(lngRow, 6) = "Ost" And .Cells(lngRow, 7) = "Fertigung" Then
For lngI = 0 To forMitarbeiter.cobStandortAuswahl.ListCount - 1
If .Cells(lngRow, 5) = forMitarbeiter.cobStandortAuswahl.List(lngI) Then Exit For
Next lngI
If lngI = forMitarbeiter.cobStandortAuswahl.ListCount Then forMitarbeiter.cobStandortAuswahl.AddItem .Cells(lngRow, 5)
End If
Next lngRow
ElseIf forMitarbeiter.optOst = True And forMitarbeiter.optZentrale = True Then
forMitarbeiter.cobStandortAuswahl.Clear
For lngRow = 3 To lngLastRow
If .Cells(lngRow, 6) = "Ost" And .Cells(lngRow, 7) = "Zentrale" Then
For lngI = 0 To forMitarbeiter.cobStandortAuswahl.ListCount - 1
If .Cells(lngRow, 5) = forMitarbeiter.cobStandortAuswahl.List(lngI) Then Exit For
Next lngI
If lngI = forMitarbeiter.cobStandortAuswahl.ListCount Then forMitarbeiter.cobStandortAuswahl.AddItem .Cells(lngRow, 5)
End If
Next lngRow
End If
End With
End Sub

Anzeige
AW: Combobox mit eindeutigen Werten aus Tab.
28.09.2022 17:26:05
Daniel
Hi
wenns so geht,ja.
ist aber viel zu aufwendig.
die ganzen IF-Blöcke sind doch bis auf "ost/west" und "Zentrale/Fertigung" gleich in der einen IF-Zeile absolut identsich.
schreib dir doch einfach die Werte in zwei Variablen:

dim Region as string
dim StandorttArt as string
If forMitarbeiter.optWest = True Then Region = "West"
If forMitarbeiter.optOst  = True Then Region = "Ost"
If forMitarbeiter.optZentrale = True Then StandortArt = "Zentrale"
If forMitarbeiter.optFertigung = True Then StandortArt = "Fertigung"
dann kannst du die ganzen IF-Blöcke mit der Abfrage der OptionButtons zu einem zusammenfassen, und machst als Abfrage dann den Vergleich

If .Cells(lngRow, 6) = Region And .Cells(lngRow, 7) = StandortArt Then
noch einfacher wirds, wenn du die Optionbuttons durch jeweils eine Listbox ersetzt:
forMitarbeiterListRegion mit "Ost" und "West" in der Auswahl
forMitarbeiterListArt mit "Zentrale" und "Fertigung" in der Auswahl.
dann fallen auch die beiden Variablen und die Zuweisungen weg, denn dann kannst du die Listboxauswahl direkt verwenden:

If .Cells(lngRow, 6) = forMitarbeiterListRegion.Value And .Cells(lngRow, 7) = forMitarbeiterListArt.Value Then
ich habe in meinen Userformen mittlerweile alle Optionbuttos durch List- oder Comboboxen ersetzt, weils für die Programmierung viel einfacher ist.
Optionbuttons nur dann, wenn wenn die getrennte Anordnung der einzelnen Optionen zwingend grafisch erforderlich ist.
Gruß Daniel
Anzeige
AW: Combobox mit eindeutigen Werten aus Tab.
29.09.2022 08:03:33
Thomas
Hallo Daniel,
danke für deine Optimierung, ich hab es dann jetzt so umgesetzt und das macht den Code tatsächlich um einiges übersichtlicher!

Public Sub StandortFuellen()
Dim lngRow As Long
Dim lngLastRow As Long
Dim lngI As Long
Dim Region As String
Dim Bereich As String
If forMitarbeiter.optWest = True Then Region = "West"
If forMitarbeiter.optOst = True Then Region = "Ost"
If forMitarbeiter.optZentrale = True Then Bereich = "Zentrale"
If forMitarbeiter.optFertigung = True Then Bereich = "Fertigung"
With tblDaten
lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
forMitarbeiter.cobStandortAuswahl.Clear
For lngRow = 3 To lngLastRow
If .Cells(lngRow, 6) = Region And .Cells(lngRow, 7) = Bereich Then
For lngI = 0 To forMitarbeiter.cobStandortAuswahl.ListCount - 1
If .Cells(lngRow, 5) = forMitarbeiter.cobStandortAuswahl.List(lngI) Then Exit For
Next lngI
If lngI = forMitarbeiter.cobStandortAuswahl.ListCount Then forMitarbeiter.cobStandortAuswahl.AddItem .Cells(lngRow, 5)
End If
Next lngRow
End With
End Sub
Die Listbox variante wäre noch schlanker, aber irgendwie gefällt mir das mit den Optionsbuttons Optisch besser :-)
Danke für deine Hilfe
Anzeige
AW: Combobox mit eindeutigen Werten aus Tab.
28.09.2022 12:59:52
Rudi
Hallo,
teste mal

Public Sub StandortFuellen()
Dim lngRow As Long
Dim lngLastRow
Dim objItem As Object
Set objItem = CreateObject("scripting.dictionary")
With tblDaten
lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
If forMitarbeiter.optWest = True And forMitarbeiter.optFertigung = True Then
For lngRow = 3 To lngLastRow
If .Cells(lngRow, 6) = "West" And .Cells(lngRow, 7) = "Fertigung" Then
If Not objItem.exists(.Cells(lngRow, 5).Value) Then
If WorksheetFunction.CountIf(Range(.Cells(2, 5), .Cells(lngRow, 5)), .Cells(lngRow, 5)) = 1 Then
forMitarbeiter.cobStandortAuswahl.AddItem = (.Cells(lngRow, 5))
objItem(.Cells(lngRow, 5).Value) = 0
End If
End If
End If
Next lngRow
End With
End Sub
Gruß
Rudi
Anzeige
AW: Combobox mit eindeutigen Werten aus Tab.
28.09.2022 15:52:58
Thomas
Hi Rudi,
hier 

 forMitarbeiter.cobStandortAuswahl.AddItem = (.Cells(lngRow, 5))
hast du den gleichen Fehler wie ich, darf nicht .Additem sein :-)
und nach Next lngRow fehlt noch ein End If
Ansonsten klappt es liefert aber leider genau wie mein Versuch das falsche Ergebnis, wie grade im Post zu vor geschrieben.
Danke für deine Hilfe
AW: Combobox mit eindeutigen Werten aus Tab.
28.09.2022 13:02:28
Nepumuk
Hallo Thomas,
teste mal:

Public Sub StandortFuellen()
Dim lngRow As Long
Dim avntValuse As Variant, vntItem As Variant
Dim objDictionary As Object
If forMitarbeiter.optWest And forMitarbeiter.optFertigung Then
Set objDictionary = CreateObject(Class:="Scripting.Dictionary")
With tblDaten
avntValuse = .Range(.Cells(3, 5), .Cells(.Rows.Count, 5).End(xlUp)).Value
End With
For Each vntItem In avntValuse
objDictionary.Item(Key:=vntItem) = vbNullString
Next
forMitarbeiter.cobStandortAuswahl.List = objDictionary.Keys
Set objDictionary = Nothing
End If
End Sub
Gruß
Nepumuk
Anzeige
AW: Combobox mit eindeutigen Werten aus Tab.
28.09.2022 16:03:17
Thomas
Hallo Nepumuk,
das klappt leider nicht, da dann alle "Orte" in die Kombobox einmal eingetragen werden, da fehlt noch so was wie ein zweites IF in form von 

If .Cells(lngRow, 6) = "West" And .Cells(lngRow, 7) = "Fertigung" Then
was dann jetzte Zeile durchläuft. hätte ich jetzt vermutet!?
Trotzdem Danke für deinen Tip
AW: Combobox mit eindeutigen Werten aus Tab.
28.09.2022 19:10:51
Nepumuk
Hallo Thomas,
dann so:

Public Sub StandortFuellen()
Dim ialngIndex As Long
Dim avntValues As Variant
Dim objDictionary As Object
If forMitarbeiter.optWest And forMitarbeiter.optFertigung Then
Set objDictionary = CreateObject(Class:="Scripting.Dictionary")
With tblDaten
avntValues = .Range(.Cells(3, 5), .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, 7)).Value
End With
For ialngIndex = LBound(avntValues, 1) To UBound(avntValues, 1)
If avntValues(ialngIndex, 2) = "West" And avntValues(ialngIndex, 3) = "Fertigung" Then _
objDictionary.Item(Key:=avntValues(ialngIndex, 1)) = vbNullString
Next
forMitarbeiter.cobStandortAuswahl.List = objDictionary.Keys
Set objDictionary = Nothing
End If
End Sub
Gruß
Nepumuk
Anzeige
AW: Combobox mit eindeutigen Werten aus Tab.
29.09.2022 08:05:11
Thomas
Hallo Nepumuk,
vielen Dank dafür, das funktioniert, leider bin ich mit dem "Scripting.Dictionary" noch nicht ganz warm geworden, da muss ich mich umbedingt mal mehr mit beschääftigen.
AW: Combobox mit eindeutigen Werten aus Tab.
28.09.2022 16:03:49
Thomas
Hallo Nepumuk,
das klappt leider nicht, da dann alle "Orte" in die Kombobox einmal eingetragen werden, da fehlt noch so was wie ein zweites IF in form von 

If .Cells(lngRow, 6) = "West" And .Cells(lngRow, 7) = "Fertigung" Then
was dann jetzte Zeile durchläuft. hätte ich jetzt vermutet!?
Trotzdem Danke für deinen Tip
AW: Combobox mit eindeutigen Werten aus Tab.
28.09.2022 13:25:14
Daniel
Hi
Du hast da einen Schreibfehler beruhend auf einem Missverständnis.
Additem ist keine Eigenschaft, die man mit einem Wert befüllt, sondern AddItem ist ein Anweisung, ein Befehl.
daher ist folgende Schreibweise in dieser Zeile anzuwenden:

forMitarbeiter.cobStandortAuswahl.AddItem .Cells(lngRow, 5).Value
Gruß Daniel
Anzeige
AW: Combobox mit eindeutigen Werten aus Tab.
28.09.2022 16:04:45
Thomas
Hallo Daniel,
ja Danke für den Hinweis, das war der Fehler, jetzt hab ich nur leider noch einen Logik fehler drin wie oben kommentiert
geht auch ohne Code.
29.09.2022 16:10:47
Daniel
Hi
prinzipell müsste das auch ganz ohne eine Zeile VBA-Code funktionieren:
1. die Optionbuttons mit einer Ausgabezelle auf dem Tabellenblatt verknüpfen. (Adresse in ControlSource angeben)
in diesen Zellen steht dann der Checkbox-Zustand mit WAHR oder FALSCH
2. in weiteren Zellen macht man dann aus dem WAHR/FALSCH per WENN-Funktion die beötigten Texte West/Ost und Zentrale/Fertigung
3. an einer freien Stelle des Tabelleblatts generiert man dann per Formel mit der Kombination aus EINDEUTIG und FILTER die erforderliche Liste
4. dann erstellt man einen Namen, der auf diese Formel referenziert und gibt dabei an, dass er sich an die größe der Ausgabe anpassen soll: =Tabelle1!A1#
(das # gibt hier an, dann sich der Bezug an das Matrixergebnis der Formel in A1 anpassen soll)
5. diesen Namen schreibt man dann als Quelle in Eigenschaft RowSource der Combobox.
ggf kann man auch den die Eindutig/Filter-Formel auch direkt in den Namen schreiben.
Gruß Daniel

Links zu Excel-Dialogen

ListBox OptionButton

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige