normale und bedingte Comboboxen

  • Excel

Es gibt 15 Antworten in diesem Thema. Der letzte Beitrag () ist von petaod.

    normale und bedingte Comboboxen

    Hallo zusammen,
    ich bin noch recht neu bei der VBA-Programmierung und habe folgendes Problem:
    Ich habe eine Excel-Datei mit den Arbeitsblättern "Kundendaten" und "Kriterien". Im Arbeitsblatt "Kundendaten" sind bereits einige Zeilen mit Informationen zu den Kunden befüllt (die erste freie Zeile ist Zeile 570).
    Ich habe nun zwei Userforms erstellt. Das erste ist für eine Personengruppe, die die erste 13 Spalten ausfüllt (Kundenname und diverse Daten). Diese können frei eingetragen werden und die Box macht keine Probleme.
    Nun habe ich eine zweite Box mit weiteren 8 Werten, die von einer anderen Personengruppe eingetragen werden sollen (deswegen 2 Boxen) und zwar in die gleiche Zeile, in der zuvor die anderen Daten eingetragen worden sind. Das allerdings auch nicht direkt in Spalte 14, die wird ausgelassen, es geht also erst in Spalte 15 weiter.
    Dieses zweite Userform enthält ComboBoxen, die ich mit Daten aus dem Arbeitsblatt "Kriterien" befüllen will. Die ersten 6 Listen sind dabei in 6 Spalten in "Kriterien" hinterlegt.
    Die letzten beiden Combobxen hängen voneinander ab. Wenn in Combobox7 ein bestimmter Wert ist, stehen in Combobx8 nur noch bestimmte andere Werte zur Verfügung.
    Meine Fragen, die sich daraus ableiten:
    Wie befülle ich die erste 6 Comboboxen? Ich bräuchte den vollständigen Code, wenn ich einfach nur ComboBox1.RowSource="Kriterien!O3:O7" mache, klappt es nicht, das Dropdownmenu bleibt leer.
    Und wie geht das mit dem bedingten Ausfüllen?
    Ich hoffe, ich habe es verständlich erklärt und ihr könnt mir helfen.
    Danke schonmal VG
    Katey

    *Topic verschoben*

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „Marcus Gräfe“ ()

    Guten Morgen Katey,

    fangen wir mal langsam an. :)

    Dynamisches befüllen der Comboboxen ist kein Problem. Ich würde dafür im UserForm_Initialize-Event der Userform die Controls in eine Schleife setzen und die Spalte entsprechend hochzählen. Die letzte Zeile einer Tabelle kannst du auch problemlos auslesen und eben über die Rowsource die Liste übergeben.
    Ich gehe davon aus, dass deine ComboBoxen fortlaufend heißen? (ComboBox1 -- ComboBox6). Wenn dem nicht so ist, müsste man das Beispiel anpassen (wäre kein großes Problem, sogar besser, wenn man die Comboboxen sinnvoll benennt!).

    Ich habe dir mal ein kleines Beispiel gebastelt

    Spoiler anzeigen

    Visual Basic-Quellcode

    1. Private Sub UserForm_Initialize()
    3. Dim vTabelle As Worksheet
    4. Dim vStartZeilenIndex As Integer
    5. Dim vSpaltenindex As Byte
    6. Dim vEndZeilenIndex As Integer
    7. Dim vControlIndex As Byte
    9. Set vTabelle = ThisWorkbook.Sheets("Tabelle1") 'Tabellennamen anpassen!!
    11. vStartZeilenIndex = 1 'Erste Zeile aus der Werte gelesen werden sollen
    13. vSpaltenindex = 4 'Comboboxindex + Spaltenindex = neue Spalte
    14. 'Beispiel: Combobox 1 + 4 = 5. Spalte (Spalte E)
    15. 'Beispiel: Combobox 8 + 4 = 12. Spalte (Spalte L)
    17. vEndZeilenIndex = vTabelle.UsedRange.SpecialCells(xlCellTypeLastCell).Row 'Letzte Zeile in der Tabelle
    19. For vControlIndex = 1 To 6 'Alle 6 Comboboxen durchlaufen
    20. Me.Controls("Combobox" & vControlIndex).RowSource = vTabelle.Name & "!" & _
    21. (Range(Cells(vStartZeilenIndex, vControlIndex + vSpaltenindex).Address & ":" & _
    22. Cells(vEndZeilenIndex, vControlIndex + vSpaltenindex).Address).Address)
    23. Next
    25. Set vTabelle = Nothing
    27. End Sub


    Anpassen musst du natürlich
    • den Tabellennamen
    • eventuell die erste Zeile der Rowsource (im Beispiel ist es 1)
    • den Spaltenindex (im Beispiel ist es 4 - geht mit Spalte E los), du müsstest 14 Schreiben, dann ist CBox1 = Spalte O


    Mit dem dynamischen Werten ist auch kein großes Problem. Da müsstest du etwas genauer beschreiben, welche Werte, wann angezeigt werden sollen bzw. wo diese Werte stehen usw. Man reagiert im Change-Event der Combobx, wertet den gewählten Wert aus und kann dann über "AddItem/RemoveItem/Clear" die Listbox neu beschreiben, erweitern, Einträge löschen.

    lg Markus
    :!: Danke an alle, die ihr Wissen freiwillig teilen und stets so kompetent & höflich sind :!:

    Hi Markus,
    erstmal danke für den Anfang, das hat schonmal geklappt. :)
    Beim Übertragen in die Tabelle hab ich noch das Problem, dass er mit dem Ausfüllen in der ersten freien Zeile ganz vorn anfägt. Ich möchte aber, dass er in der Zeile anfängt, in der Spalte 15 noch frei ist. (Also nicht ab A570 befüllen sondern ab O570)
    Der aktuell Code sieht so aus:

    Quellcode

    1. Private Sub CommandButton1_Click()
    2. Dim z As Integer
    3. z = Range("A570").End(xlUp).Row + 1
    4. For sp = 1 To 8
    5. Cells(z, sp) = Controls("ComboBox" & sp).Text
    6. Next sp
    7. Unload Me
    8. End Sub

    Den habe ich einfach von der ersten Box kopiert und aus TextBox Combobox gemacht. Aber welcher Teil muss angepasst werden, damit er mit dem Befüllen erst in O570 beginnt?

    Bei den letzten beiden Comboboxen ist es so, dass die Daten nach folgendem Schema vorliegen

    Quellcode

    1. Branche Stufe 1 Industrie Wohnungswirtschaft Stadtkonzern Gewerbekunden
    2. Branche Stufe 2 bla blub1 abcde1
    3. bla blub2 abcde2
    4. abcde3
    5. ....
    6. abcde14

    Ich möchte also, wenn ich bei Branche Stufe 1 "Industrie" auswähle, dass bei Branche Stufe 2 nur noch "bla blub1" und "bla blub2" zur Auswahl sind. Ich weiß nicht, ob es das einfacher macht, aber es ist nicht geplant, an den Branchen was zu ändern, das heißt also, dass sich Anzahl und Inhalt der Zellen nicht ändern wird.
    Danke schonmal im voraus. :)

    Edit: Mir ist grad aufgefallen, dass er bei der ersten Userform nicht automatisch die nächste Zeile befüllt, wenn die 570 voll ist. Wie erklär ich ihm das?

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „Katey“ ()

    Fangen wir mal bei deinem Code an.
    Der Befehl CELLS erwartet zwei Parameter. Als ersten den Zeilenindex und als zweiten den Spaltenindex

    In deiner Schleife übergibst du als SPALTE den Wert 1-8. Du befüllst also die Spalten A-H
    Möchtest du die Spalten O-V, musst du zu dem Wert sp bei der Übergabe an Cells
    die Zahl 14 hinzu addieren

    Also

    Visual Basic-Quellcode

    1. Cells(z, sp+14) = Controls("ComboBox" & sp).Text


    Du hattest geschrieben, dass mit der ersten Userform Spalten A-M in einer neuen Zeile beschrieben werden und dann in der zweiten Form Spalten O-V in der selben Zeile? Dann müsstest du bei der zweiten Form die letzte Zeile nicht +1 rechnen, weil er die Werte dann natürlich in die nächste freie Zeile schreiben würde.

    Mit den dynamischen Comboboxen hättest du es also so, dass in der ersten alle Einträge der 1. Zeile eines Tabellenblattes erscheinen und in der Zweiten Combobox jeweils nur die Einträge ab Zeile 2 der jeweiligen Spalte?

    Das würde ich so bewerkstelligen:
    Spoiler anzeigen

    Visual Basic-Quellcode

    1. Set vTabelle = ThisWorkbook.Sheets("Tabelle3")
    3. Me.ComboBox7.List = Application.WorksheetFunction.Transpose(Range(vTabelle.Name & "!" & _
    4. Range("A1:" & vTabelle.Cells(1, 1).End(xlToRight).Address).Address).Value)
    6. Set vTabelle = Nothing


    In dem Beispiel schaut er in Tabelle3 Beginnend von A1 wieviel Spaltenüberschriften vorhanden sind und fügt diese als Auswahl hinzu.

    Auf der Combobox7 würde ich dann das Change-Event so befüllen:

    Spoiler anzeigen

    Visual Basic-Quellcode

    1. Private Sub ComboBox7_Change()
    3. Dim vTabelle As Worksheet
    5. If Me.ComboBox7.ListIndex <> -1 Then
    6. Me.ComboBox8.RowSource = ""
    7. Me.ComboBox8.Text = ""
    8. Set vTabelle = ThisWorkbook.Sheets("Tabelle3")
    10. Me.ComboBox8.RowSource = vTabelle.Name & "!" & Cells(2, Me.ComboBox7.ListIndex + 1).Address & ":" & _
    11. Cells(Cells(65536, Me.ComboBox7.ListIndex + 1).End(xlUp).Row, Me.ComboBox7.ListIndex + 1).Address
    13. Set vTabelle = Nothing
    14. End If
    15. End Sub


    Der code sieht wirrer aus, als er ist. :) In der Combobox7 hast du einen Index.
    1. Wert = 0 ;
    2. Wert = 1 usw

    Wenn man jetzt den 1. Eintrag auswählt, wirft .Listindex eine 0 zurück. Die 0 entspricht aber der 1. Spalte also A also 1. Deswegen rechne ich den Listindex +1
    Wenn man den 3. Eintrag wählt, wirft .Linstindex eine 2 zurück. Da ich aber Spalte C will, wird der Wert +1 gerechnet.

    Visual Basic-Quellcode

    1. Cells(65536, Me.ComboBox7.ListIndex + 1).End(xlUp).Row
    2. 'Liefert die letzte beschriebene Zeile (von Zeile 65536 ausgehend)


    lg

    *edit*
    Falls du keine Einträge unter einer Kategorie hast, erhältst du eine fehlerhafte Auswahl.. diesen Fehler müsstest du noch prüfen und berücksichtigen! ;)
    :!: Danke an alle, die ihr Wissen freiwillig teilen und stets so kompetent & höflich sind :!:

    Also mein Code sieht jetzt so aus:

    Quellcode

    1. Private Sub CommandButton2_Click()
    2. Unload Me
    3. End Sub
    4. Private Sub CommandButton1_Click()
    5. Dim z As Integer
    6. z = Range("A570").End(xlUp).Row
    7. For sp = 1 To 8
    8. Cells(z, sp + 14) = Controls("ComboBox" & sp).Text
    9. Next sp
    10. Unload Me
    11. End Sub
    12. Private Sub UserForm_Initialize()
    14. Dim vTabelle As Worksheet
    15. Dim vStartZeilenIndex As Integer
    16. Dim vSpaltenindex As Byte
    17. Dim vEndZeilenIndex As Integer
    18. Dim vControlIndex As Byte
    20. Set vTabelle = ThisWorkbook.Sheets("Kriterien") 'Tabellennamen anpassen!!
    22. vStartZeilenIndex = 3 'Erste Zeile aus der Werte gelesen werden sollen
    24. vSpaltenindex = 14 'Comboboxindex + Spaltenindex = neue Spalte
    25. 'Beispiel: Combobox 1 + 4 = 5. Spalte (Spalte E)
    26. 'Beispiel: Combobox 8 + 4 = 12. Spalte (Spalte L)
    28. vEndZeilenIndex = 7
    30. For vControlIndex = 1 To 6 'Alle 6 Comboboxen durchlaufen
    31. Me.Controls("Combobox" & vControlIndex).RowSource = vTabelle.Name & "!" & _
    32. (Range(Cells(vStartZeilenIndex, vControlIndex + vSpaltenindex).Address & ":" & _
    33. Cells(vEndZeilenIndex, vControlIndex + vSpaltenindex).Address).Address)
    34. Next
    36. Set vTabelle = Nothing
    37. Set vTabelle = ThisWorkbook.Sheets("Kriterien")
    39. Me.ComboBox7.List = Application.WorksheetFunction.Transpose(Range(vTabelle.Name & "!" & _
    40. Range("O15:" & vTabelle.Cells(1, 1).End(xlToRight).Address).Address).Value)
    42. Set vTabelle = Nothing
    44. End Sub
    46. Private Sub ComboBox7_Change()
    48. Dim vTabelle As Worksheet
    50. If Me.ComboBox7.ListIndex <> -1 Then
    51. Me.ComboBox8.RowSource = ""
    52. Me.ComboBox8.Text = ""
    53. Set vTabelle = ThisWorkbook.Sheets("Kriterien3")
    55. Me.ComboBox8.RowSource = vTabelle.Name & "!" & Cells(2, Me.ComboBox7.ListIndex + 1).Address & ":" & _
    56. Cells(Cells(65536, Me.ComboBox7.ListIndex + 1).End(xlUp).Row, Me.ComboBox7.ListIndex + 1).Address
    58. Set vTabelle = Nothing
    59. End If
    60. End Sub


    Die Tabelle, in der die Daten für die Boxen 7 und 8 hinterlegt sind, fängt nicht bei A1 an sondern bei O15, deswegen habe ich das entsprechend angepasst, aber es klappt trotzdem nicht. Was mache ich falsch?
    P.S.: Danke für deine Geduld ^^

    Bevor ich mir jetzt deinen Code ansehe, vorab die Fragen bzw. auch als Hinweis für die Zukunft zu verstehen:
    Was genau funktioniert nicht? Kommt eine Fehlermeldung? Werden falsche Werte angezeigt? Werden gar keine angezeigt?
    ^^
    :!: Danke an alle, die ihr Wissen freiwillig teilen und stets so kompetent & höflich sind :!:

    ;)
    Bei der combobox7.list hast du zwar den richtigen Ausgangswert bearbeitet, nicht aber die ZielZelle

    Visual Basic-Quellcode

    1. 'FALSCH
    2. 'Me.ComboBox7.List = Application.WorksheetFunction.Transpose(Range(vTabelle.Name & "!" & _
    3. ' Range("O15:" & vTabelle.Cells(1, 1).End(xlToRight).Address).Address).Value)
    4. 'ZUM TESTEN MAL AUSFÜHREN
    5. msgbox Range("O15:" & vTabelle.Cells(1, 1).End(xlToRight).Address


    Führe mal diesen Code aus. Dann verstehst du, was passiert ist.
    vTabelle.Cells(1, 1).End(xlToRight).Address bedeutet, dass er von Zelle(1,1) (A1) nach Rechts bis zur letzten Zelle springt (von A1 ausgehend).

    Richtig lautet die Zeile als:

    Visual Basic-Quellcode

    1. Me.ComboBox7.List = Application.WorksheetFunction.Transpose(vTabelle.Range(vTabelle.Name & "!" & _
    2. Range("O15:" & vTabelle.Cells(15, 15).End(xlToRight).Address).Address).Value)
    4. 'Cells(15, 15) steht für O15
    5. 'Beispiel Cells(14,16) steht für P14


    Weiterhin musst du die Zeile anpassen

    Visual Basic-Quellcode

    1. 'FALSCH
    2. Me.ComboBox8.RowSource = vTabelle.Name & "!" & Cells(2, Me.ComboBox7.ListIndex + 1).Address & ":" & _
    3. Cells(Cells(65536, Me.ComboBox7.ListIndex + 1).End(xlUp).Row, Me.ComboBox7.ListIndex + 1).Address

    Richtig lautet diese

    Visual Basic-Quellcode

    1. 'RICHTIG
    2. Me.ComboBox8.RowSource = vTabelle.Name & "!" & Cells(16, Me.ComboBox7.ListIndex + 15).Address & ":" & _
    3. Cells(vTabelle.Cells(65536, Me.ComboBox7.ListIndex + 15).End(xlUp).Row, Me.ComboBox7.ListIndex + 15).Address
    5. 'cells(16, Me.ComboBox7.ListIndex + 15) Bedeutet, wenn du ersten Eintrag auswählst: O16


    Wenn du nicht nachvollziehen kannst, was bestimmte Teile bedeuten, kannst du zum Beispiel schreiben

    Visual Basic-Quellcode

    1. Debug.print vTabelle.Name & "!" & Cells(16, Me.ComboBox7.ListIndex + 15).Address


    Im Direktfenster (Strg+G), steht dann der Wert dahinter.

    Sollte so passen, falls ich keinen Patzer drin hab! :huh:
    :!: Danke an alle, die ihr Wissen freiwillig teilen und stets so kompetent & höflich sind :!:

    Hab deine Änderungen alle so übernommen. In ComboBox7 stehen jetzt die richtigen Werte von Stufe 1 drin.
    Aber wenn ich Stufe 2 auswählen will, bekomme ich "Laufzeitfehler '9': Index außerhalb des gültigen Bereichs".
    Wenn ich da auf Debuggen klicke, markiert er mir Zeile 53 (Set vTabelle = ThisWorkbook.Sheets("Kriterien3"))

    Das bedeutet eigentlich, dass er das Tabellenblatt nicht finden kann.
    Befindet sich das Tabellenblatt in der selben Datei (thisworkbook), in der der Code ausgeführt wird?

    Sollte jedenfalls klappen:
    Set vTabelle = ThisWorkbook.Sheets("Kriterien3")
    :!: Danke an alle, die ihr Wissen freiwillig teilen und stets so kompetent & höflich sind :!:

    Ja, man sollte dann doch wissen, wie die eigenen Arbeitsblätter heißen... ^^
    Nu klappt fast alles, nur eins noch. Wie springe ich in die nächste Zeile?
    So sieht der Code aus (der ist diesmal von UserForm1)

    Quellcode

    1. Private Sub CommandButton1_Click()
    2. Dim z As Integer
    3. z = Range("A570").End(xlUp).Row + 1
    4. For sp = 1 To 13
    5. Cells(z, sp) = Controls("Textbox" & sp).Text
    6. Next sp
    7. Unload Me
    8. End Sub

    und aktuell ist es so, dass er das erste mal, wenn ich Daten eingebe, diese in Zeile 570 eingibt. Wenn ich das zweite mal Daten eingebe, fügt er sie in Zeile 3 (da fängt die Tabelle an) ein. Aber das zweite mal soll ja in Zeile 571 (usw.) Was muss ich dann noch daran ändern?

    Weißt du was:

    Visual Basic-Quellcode

    1. Range("A570").End(xlUp).Row + 1

    bedeutet?

    Klicke mal bitte händisch in Zelle A570 und drücke STRG+Cursor_Taste_hoch
    Füge mal in A570 einen Wert ein und drücke nochmal STRG+Cursor_Taste_hoch
    ;)

    Ändere einfach die Ausgangszeile in A65536 (war bis 2007 die letzte Zeile)
    Also

    Visual Basic-Quellcode

    1. Range("A65536")


    lg
    :!: Danke an alle, die ihr Wissen freiwillig teilen und stets so kompetent & höflich sind :!:

    Es funzt alles. :)
    Danke für die tolle Hilfe. :D
    Edit: Falls es nicht zu schwierig ist: Kann man es so einstellen, dass der Cursor automatisch zu der Zeile geht, die gerade neu generiert wurde?
    Also beim ersten Userform in A570 und beim zweiten O570 (bzw. die aktuelle Zeile eben)?

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „Katey“ ()

Facebook
Telefonspende