Problem mit automatischem Ausfüllen einer ComboBox per VBA

[VBA4Beginner]

Hallo,

ich bin neu hier und auch nicht ganz so bewandert in VBA. Allerdings kann ich mir hin und wieder auch vorgegebenen Code für meine Bedürfnisse anpassen, was in diesem Fall aber nicht ganz nach meinen Vorstellungen funktioniert.
 
Ausgangslage:
Im Tabellenblatt „Daten für die Auswertung“ stehen in meiner angehängten Beispieldatei in jeder Spalte unsortierte und auch doppelt vorkommende Werte.

Aufgabe:
Im Tabellenblatt „Kriterien“ möchte ich nun für jede einzelne Spalte in „Daten für die Auswertung“ ein eigenes Dropdown-Feld zur Auswahl der Daten haben. Hierbei sollen die Daten je nach Art entweder numerisch oder alphabetisch sortiert sein und keine Duplikate innerhalb jedes Dropdown-Feldes enthalten.

Mein Lösungsansatz:
Über ein im Internet gefundenes und von mir angepasstes Makro pro Spalte lasse ich mir die Daten in das Tabellenblatt „Hilfsdaten“ entsprechend sortiert kopieren. Das funktioniert soweit auch ganz prima.

Code:

Sub Auftraggeber_ohne_duplikate_sortiert_kopieren()
 
  Dim lngLetzteAuftraggeber As Long
 
  Worksheets("Hilfsdaten").Columns("A").ClearContents 'Inhalte der Spalte A in Tabelle "Hilfsdaten" löschen
 
 'Mit dem Spezialfilter Spalte M aus Tabelle "Daten für die Auswertung" ohne Duplikate
 'in Spalte A der Tabelle "Hilfsdaten" kopieren
  With Sheets("Daten für die Auswertung")
     lngLetzteAuftraggeber = .Cells(.Rows.Count, "M").End(xlUp).Row
      Sheets("Daten für die Auswertung").Range("M1:M" & lngLetzteAuftraggeber).AdvancedFilter Action:=xlFilterCopy, _
          CopyToRange:=Sheets("Hilfsdaten").Range("A1"), Unique:=True
  End With
 
 'aufgezeichneter und nachbehandelter Code zum Sortieren der Spalte A
  With Sheets("Hilfsdaten")
     lngLetzteAuftraggeber = .Cells(.Rows.Count, "A").End(xlUp).Row
     .Range("A1:A" & lngLetzteAuftraggeber).ClearFormats   'Formate löschen
      .Sort.SortFields.Clear
      .Sort.SortFields.Add Key:=Range("A1"), _
          SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
     With .Sort
         .SetRange Range("A1:A" & lngLetzteAuftraggeber)
         .Header = xlYes
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
     End With
     lngLetzteAuftraggeber = .Cells(.Rows.Count, "A").End(xlUp).Row
  End With

End Sub

Die in Spalte A angezeigte Liste bekomme ich wiederum über einen gefundenen und modifizierten VBA-Code in die ComboBox „CmbBoxAuftraggeber“ auf dem Tabellenblatt „Kriterien“. Wenn ich dort einen Wert auswähle, schreibt er sich automatisch in eine extra Zelle mit Namen „krtAuftraggeber“ auf dem gleichen Tabellenblatt, das ich dann hinterher in weiteren Schritten dort auslesen möchte.

Code:

Private Sub CmbBoxAuftraggeber_DropButtonClick()
'Variable deklarieren
Dim WiederholungenAuftraggeber As Integer
'Schleife zum Füllen der ComboBox mit den Daten aus Blatt "Hilfsdaten"
'Spalte A ab Zeile 2 bis zur letzten gefüllten Zeile
For WiederholungenAuftraggeber = 2 To Sheets("Hilfsdaten").Range("A65536").End(xlUp).Row
CmbBoxAuftraggeber.AddItem Sheets("Hilfsdaten").Cells(WiederholungenAuftraggeber, 1)
Next
End Sub

Private Sub CmbBoxAuftraggeber_Click()
Range("krtAuftraggeber").Value = CmbBoxAuftraggeber.Value
End Sub

So weit, so gut.

Mein Problem:
Wenn ich jetzt versuche, den gleichen Weg für die ComboBox „cmbBoxAuftragsart“ analog einzusetzen, bekomme ich in der ComboBox nicht die von mir gewünschten Werte, sondern immer die drei ersten Einträge aus Spalte A und nicht aus Spalte B zur Auswahl. Hier mal der "duplizierte Code, der nicht das gewünschte Ergebnis bringt:

Code:

Private Sub CmbBoxAuftragsart_DropButtonClick()
'Variable deklarieren
Dim WiederholungenAuftragsart As Integer
'Schleife zum Füllen der ComboBox mit den Daten aus Blatt "Hilfsdaten"
'Spalte B ab Zeile 2 bis zur letzten gefüllten Zeile
For WiederholungenAuftragsart = 2 To Sheets("Hilfsdaten").Range("B65536").End(xlUp).Row
CmbBoxAuftragsart.AddItem Sheets("Hilfsdaten").Cells(WiederholungenAuftragsart, 1)
Next
End Sub


Private Sub CmbBoxAuftragsart_Click()
Range("krtAuftragsart").Value = CmbBoxAuftragsart.Value
End Sub

 
Ich gehe mal ganz stark davon aus, dass es nur ein minimales Problem in der Syntax ist, da der Code ja bei der ersten ComboBox funktioniert, aber dafür sind meine VBA-Kenntnisse dann doch zu gering.
 
Ich würde mich sehr freuen, wenn mir jemand hier helfen und vielleicht sogar meinen Fehler finden könnte J Gerne nehme ich auch Tipps entgegen, wie ich mein Vorgehen noch optimieren könnte. Ganz wichtig scheint mir auch noch die Tatsache zu sein, dass es sich im Original um deutlich mehr Datensätze (ca. 30.000) handelt, als im Beispiel.
  2015-09-10 Filtern und Listen_ComboBox_neutral.xlsm (Größe: 80,4 KB / Downloads: 8)
 
Herzlichen Dank für die Unterstützung schon mal vorab und falls ich irgendetwas vergessen habe zu erklären, dann lasst es mich wissen
VBA4Beginner

[Steffl]

Hallo,

mal ungetestet anstatt

Code:

CmbBoxAuftragsart.AddItem Sheets("Hilfsdaten").Cells(WiederholungenAuftragsart, 1)

versuchs mal so

Code:

CmbBoxAuftragsart.AddItem Sheets("Hilfsdaten").Cells(WiederholungenAuftragsart, 2)

[VBA4Beginner]

Hallo Stefan,
der Tipp war goldrichtig :25:  Danke!
Offensichtlich ist die Ziffer der Hinweis auf die Spalte.

Nun stellt sich nur noch ein anderes Problem:
in der Combobox für die Auftragsart tauchen nun nicht nur einfach die laut Liste gewünschten 3 Einträge auf, sondern wiederholen sich immer wieder.
[
Bild bitte so als Datei hochladen: Klick mich!
]
Offensichtlich scheint das so oft zu sein, wie Zeilen in Spalte A des Tabellenblattes "Hilfsdaten" sind.
Liegt sicherlich an der Ermittlung der letzten Zeile in diesem Teil des Codes

Code:

For WiederholungenAuftragsart = 2 To Sheets("Hilfsdaten").Range("B65536").End(xlUp).Row

Hat noch jemand eine Idee, wie ich das lösen könnte?

Wiederum Danke vorab für die Unterstützung und viele Grüße
Tamara

[RPP63]

Hallo!
Eine wahre Fundgrube zum schnellen Füllen von Combo- oder ListBoxes findest Du hier:
http://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

Scrolle nach unten bis unique values in a column (unsortiert) oder unique sorted values in a column.

Bedenke, dass snb grundsätzlich auf Variablendeklarationen verzichtet!
Falls Du mit Option Explicit arbeitest, musst Du sie ergänzen.

Gruß Ralf

[VBA4Beginner]

Hallo Ralf,

vielen Dank für deine schnelle Antwort!

Wenn ich das richtig verstehe, dann würde ich mir durch einen dieser Codes das Tabellenblatt "Hilfsdaten" ersparen, oder?
Leider komme ich "Anfänger" an dieser Stelle aber nicht weiter, da ich wirklich nicht weiß, wie ich die in meine Datei einsetzen müsste - geschweige denn, was du mit "Option Explicit" meinst :48:
Hätte vielleicht jemand Zeit, mir das genauer zu erklären?

Danke vorab und viele Grüße
Tamara

[RPP63]

Hi Tamara!
Ich bin da ein wenig (für einen "Neuling" in VBA) über das Ziel hinausgeschossen!
Ich habe mir die Datei jetzt mal heruntergeladen.

Bei jedem Klick auf den Dropdown fügst Du erneut die Items hinzu, die Liste wird also lang und länger ...

Eine Lösung:
Lösche die Dropdown.List bei jedem Neufüllen mit der Codezeile
CmbBoxAuftragsart.Clear

Als Gesamt-Code:

Code:

Private Sub CmbBoxAuftragsart_DropButtonClick()
'Variable deklarieren
Dim WiederholungenAuftragsart As Integer
'Schleife zum Füllen der ComboBox mit den Daten aus Blatt "Hilfsdaten"
'Spalte B ab Zeile 2 bis zur letzten gefüllten Zeile
CmbBoxAuftragsart.Clear
For WiederholungenAuftragsart = 2 To Sheets("Hilfsdaten").Range("B65536").End(xlUp).Row
CmbBoxAuftragsart.AddItem Sheets("Hilfsdaten").Cells(WiederholungenAuftragsart, 2)
Next
End Sub

Das machst Du analog auch im CmbBoxAuftraggeber_DropButtonClick()

Gruß Ralf

[VBA4Beginner]

Hallo Ralf,

cool, dass du mir "Anfänger" so lieb hilfst
Mir ist das natürlich gar nicht aufgefallen, dass es immer mehr wurde in der Liste :33:
Die Ergänzung um deine Zeile hat geholfen!
Ich möchte jetzt auch nicht übermütig werden, aber bekommt man es irgendwie noch hin, dass in der Combobox selbst der gewählte Wert bis zum nächsten anklicken auch stehen bleibt?
Bei mir überträgt er den gewählten Wert ganz brav in das gewünschte Feld, aber die Combobox ist direkt "leer".

Viele Grüße
Tamara

[RPP63]

Hi!
Mach mal anders, dann gewöhnst Du Dir auch gleich an, dass man Comboboxen am besten (vor allem schnell) per .List-Eigenschaft mit einem Range füllt.
Zunächst habe ich für die CBs die LinkedCell-Eigenschaft benutzt, um die verknüpfte Zelle anzugeben.
Der gesamte Code in der Tabelle Kriterien ist jetzt:

Code:

Private Sub Worksheet_Activate()
Dim Auftraggeber As Range, Auftragsart As Range
With Worksheets("Hilfsdaten")
  Set Auftraggeber = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlDown).Row)
  Set Auftragsart = .Range("B2:B" & .Cells(.Rows.Count, 2).End(xlDown).Row)
End With
CmbBoxAuftraggeber.List = Auftraggeber.Value
CmbBoxAuftragsart.List = Auftragsart.Value
End Sub

Private Sub CmdStartdatum_Click()
frmCalendar.Show
   Range("krtStartdatum") = g_datCalendarDate
End Sub

Private Sub CmdEnddatum_Click()
frmCalendar.Show
   Range("krtEnddatum") = g_datCalendarDate
End Sub

Gruß Ralf

[VBA4Beginner]

Hallo Ralf,

du bist auf jeden Fall mein Held des Tages, wenn nicht gar der Woche :18:

Dass es so kurz und knackig geht hätte ich gar nicht gedacht und das beste an der Geschichte ist, dass ich sogar verstehe, was der Code so vor sich hinarbeitet!
Die LinkedCell-Eigenschaft habe ich übrigens auch gefunden
So lernt man eben...

Ganz herzlichen Dank nochmal für die große Hilfe und viele Grüße
Tamara

PS: hier komme ich sicherlich noch häufiger vorbei :21:

[RPP63]

Danke fürs Feedback!
Allerdings habe ich da einen blöden Fehler:
Nicht:

Code:

Set Auftraggeber = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlDown).Row)

sondern entweder:

Code:

Set Auftraggeber = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)

oder:

Code:

Set Auftraggeber = .Range("A2:A" & .Cells(2, 1).End(xlUp).Row)

Gruß Ralf