Excel VBA: Mehrere Comboboxen auf UF auf Change überwachen

Hallo allerseits

Um im Endeffekt auf einem Excel-Sheet nur ganz bestimmte Spalten anzusprechen in die dann was geschrieben werden soll habe ich mir eine Userform entworfen.
Auf der rechten Seite habe ich 4 Comboboxen für die ersten 4 Spalten (A-D) die jeweils den Inhalt ohne doppelte Einträge ausgibt.
Auf der linken Seite befinden sich 20 Label und 20 Textboxen für die Spalten E-X.
Ziel ist nun über die Auswahl in den Comboboxen aus dem ListIndex eine eindeutige 4 stellige Zahl zu generieren hinter der sich jeweils eine ganz bestimmte Kombination aus den Spalten E-X verbirgt. Genau diese sollen dann auf der linken Seite zur Verfügung stehen, der Rest soll deaktiviert sein.

Mit dem nachfolgenden Code bin ich zumindest soweit, daß diese Zahlenkombination erzeugt wird, jedoch m.E. etwas umständlich bzw. unglücklich gelöst, da ich alle Comboboxen einzel abfrage.
Wie kann die umfassender gelöst werden bzw. der Change für alle Comboboxen überwacht werden?

Private Sub UserForm_Initialize()

Dim objDic As Object
Dim lngZ As Long

Set objDic = CreateObject("Scripting.Dictionary")  
 
For i = 1 To 4
    For lngZ = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        objDic(Cells(lngZ, i).Value) = 0
    Next
 
    With Me.Controls("ComboBox" & i)  
        .List = objDic.keys
        .ListIndex = 0
        .SetFocus:
        .SelStart = 0:
        .SelLength = Len(.Text)
    End With
    objDic.RemoveAll
Next

End Sub

Private Sub ComboBox1_Change()
    Controls("cbocon" & 1).Caption = Controls("Combobox" & 1).ListIndex  

End Sub

Private Sub ComboBox2_Change()
    Controls("cbocon" & 2).Caption = Controls("Combobox" & 2).ListIndex  
End Sub

Private Sub ComboBox3_Change()
    Controls("cbocon" & 3).Caption = Controls("Combobox" & 3).ListIndex  
End Sub

Private Sub ComboBox4_Change()
    Controls("cbocon" & 4).Caption = Controls("Combobox" & 4).ListIndex  
End Sub

Ich freue mich über jeden Tipp und Lösungshinweis der mir hilft dieses kleine Eingabetool zum Ende zu führen.

Grüße
Juwee

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 267925

Url: https://administrator.de/contentid/267925

Ausgedruckt am: 22.11.2024 um 12:11 Uhr

7 Kommentare

Neuester Kommentar

Moin,
etwa so:

Private Sub ComboBox1_Change()
    UpdateLabels
End Sub

Private Sub ComboBox2_Change()
    UpdateLabels
End Sub

Private Sub ComboBox3_Change()
    UpdateLabels
End Sub

Private Sub ComboBox4_Change()
    UpdateLabels
End Sub

Sub UpdateLabels()
    For i = 1 To 4
        Controls("cbocon" & i).Caption = Controls("Combobox" & i).ListIndex  
    Next
End Sub

Gruß jodel32

Moin Moin jodel32,

vielen Dank für deinen Tipp, der immerhin schon mal in eine Richtung geht.
Ich habe natürlich in der letzten Zeit auch selbst recherchiert und bin dabei hierauf gestoßen:
http://www.office-loesung.de/ftopic408497_0_0_asc.php
Demnach kann das wohl auch über eine Klasse gelöst werden.
Ich hab versucht dies umzusetzen, bin aber ziemlich gescheitert.
Geht das überhaupt in die richtige Richtung?

Gruß
Juwee

Hallo Juwee, Willkommen auf Administrator.de!
Hier hast du ein Demo-Sheet an dem du es dir anhand eines Beispiels mit einer Klasse ab schauen kannst:
demo_combo_class_267925.xlsm

Öffne im Sheet die Form und ändere dann in den Comboboxen die Werte.

Grüße Uwe

Hallo zusammen!

Und ergänzend zu Uwe's Ansatz, das Ganze dann in etwa so

Klassenmodul "EventsKlasse":

Option Explicit

Public WithEvents CBoxObject As MSForms.ComboBox

Private Sub CBoxObject_Change()
    UserForm1.Controls(CBoxObject.Tag).Caption = CBoxObject.ListIndex
End Sub

Wobei die zugehörigen "cbocon#"-Namen im jeweiligen ComboBox.Tag stehen...

Der UserForm(1)-Code:

Option Explicit

Private objCBoxControl As New Collection

Private Sub UserForm_Initialize()
    Dim objDic As Object, objCBox As EventsKlasse, lngZ As Long, i As Integer

    For i = 1 To 4
        Set objCBox = New EventsKlasse
        Set objCBox.CBoxObject = Me.Controls("ComboBox" & i)  
        objCBoxControl.Add objCBox
    Next
    
    Set objDic = CreateObject("Scripting.Dictionary")  
 
    For i = 1 To 4
        For lngZ = 2 To Cells(Rows.Count, 1).End(xlUp).Row
            objDic(Cells(lngZ, i).Value) = 0
        Next
     
        With Me.Controls("ComboBox" & i)  
            .List = objDic.Keys
            .ListIndex = 0
            .SetFocus:
            .SelStart = 0:
            .SelLength = Len(.Text)
        End With
        objDic.RemoveAll
    Next
End Sub

Grüße Dieter

Guten Morgen ...
vielen Dank euch beiden.
Funktioniert bestens

Dann also auf zu neuen Ufern ...

Bis demnächst!

Gruß
Juwee

Hallo,

nochmal eine Frage zur EventKlasse ...
Mit dem beiliegenden Code möchte ich die Labels und Combos 0-4 steuern analog der Auswahl die in Combobox5 (Sheets) getätigt wird.
Wie baue ich den Handler ein, damit die Labels und Combos sich ändern, wenn sich auch die Auswahl des entsprechenden Sheets ändert?

Option Explicit

Private objCBoxControl As New Collection

Private Sub UserForm_Initialize()
    Dim objDic As Object, objCBox As EventClass, lngZ As Long, i As Integer, ws As Worksheet, selSheet As String
    
    For Each ws In Worksheets
    
        With Me.Controls("ComboBox5")  
            .AddItem ws.Name
            .ListIndex = 0
            .SetFocus:
            .SelStart = 0:
            .SelLength = Len(.Text)
        End With

    Next ws

    Set objCBox = New EventClass
    Set objCBox.CBoxObject = Me.Controls("ComboBox5")  
    objCBoxControl.Add objCBox
    
    selSheet = ComboBox5.Value
    
    For i = 0 To 4
    
        Set objDic = CreateObject("Scripting.Dictionary")  
 
            For lngZ = 2 To Cells(Rows.Count, 1).End(xlUp).Row
                objDic(Cells(lngZ, i + 1).Value) = 0
            Next
            
            Me.Controls("Label" & i).Caption = Worksheets(selSheet).Cells(1, i + 1)  
        
            With Me.Controls("ComboBox" & i)  
                .List = objDic.Keys
                .ListIndex = 0
                .SetFocus:
                .SelStart = 0:
                .SelLength = Len(.Text)
            End With
            
            objDic.RemoveAll
            
    Next
      
End Sub

Wenn ich das so mache, dann wird zwar alles erstmal korrekt eingelesen, ich bekommen jedoch einen Fehler (Ungültiges Argument) im Klassenmodul "EventClass" an dieser Stelle:

Private Sub CBoxObject_Change()
    UserForm.Controls(CBoxObject.Tag).Caption = CBoxObject.ListIndex
End Sub

LG und einen schönen Vatertag morgen an alle die Papa sind und die die es bestimmt irgendwann noch werden (wollen).

Juwee

Hallo Juwee!

Würde in etwa so gehen (UserForm-Code):

Option Explicit

Private objCBoxControl As New Collection

Private Sub UserForm_Initialize()
    Dim oWks As Worksheet, objCBox As EventsKlasse, i As Integer

    For i = 1 To 4  'ComboBox 1-4 für Eventsklasse registrieren  
        Set objCBox = New EventsKlasse
        Set objCBox.CBoxObject = Me.Controls("ComboBox" & i)  
        objCBoxControl.Add objCBox
    Next
    
    With Me.ComboBox5
        For Each oWks In ThisWorkbook.Sheets
           .AddItem oWks.Name
        Next
       .ListIndex = 0
       .SetFocus
       .SelStart = 0
       .SelLength = Len(.Text)
    End With
End Sub

Private Sub ComboBox5_Change()
    Dim objDic As Object, lngZ As Long, i As Integer
    
    With Me.ComboBox5
        Sheets(.Value).Activate
        Me.Label1.Caption = .Value
    End With

    Set objDic = CreateObject("Scripting.Dictionary")  
 
    For i = 1 To 4
        For lngZ = 2 To Cells(Rows.Count, 1).End(xlUp).Row
            objDic(Cells(lngZ, i).Value) = 0
        Next
     
        With Me.Controls("ComboBox" & i)  
            .List = objDic.Keys
            .ListIndex = 0
        End With
        objDic.RemoveAll
    Next
End Sub