Class of Comboboxes in UserForm

cr731

Well-known Member
Joined
Sep 17, 2010
Messages
611
I have a userform with 5 comboboxes that I want to add to a class module so that my code runs whenever a box value is changed. I have this so far:

On Userform Initialize:

Code:
'Assign all Comboboxes to ComboBoxes class module so that if any Combobox is clicked, _
    Class module code will run
    Dim ComboBoxControl As Control
    Dim ComboBoxClass As Class_ComboBoxes
    Set ComboBoxCollection = New Collection
    For Each ComboBoxControl In Me.Controls
        If TypeName(ComboBoxControl) = "ComboBox" Then
            Set ComboBoxClass = New Class_ComboBoxes
            Set ComboBoxClass.ComboBox = ComboBoxControl
            ComboBoxCollection.Add ComboBoxClass
        End If
    Next ComboBoxControl

In Class Module ("Class_Comboboxes")

Code:
Public WithEvents ComboBox As MSForms.ComboBox
Private Sub ComboBox_Change()
    MsgBox "changed"
End Sub

When the userform first initializes, I get a message box "Changed" for each one as they get populated. But after that, the message box does not appear when I change one of their values. I must be missing something?
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
in the userform

Code:
Private cComboBoxHandler() As clsCombo


Private Sub UserForm_Initialize()
Dim Cntrl As MSForms.Control, K As Long
For Each Cntrl In Me.Controls
    If TypeOf Cntrl Is MSForms.ComboBox Then
        ReDim Preserve cComboBoxHandler(0 To K) As clsCombo
        Set cComboBoxHandler(K) = New clsCombo
        Set cComboBoxHandler(K).MyComboBox = Cntrl
        K = 1 + K
    End If
Next Cntrl
End Sub


in the class (called clsCombo)

Code:
Private WithEvents cmbBox As MSForms.ComboBox


Private Sub cmbBox_Change()
    MsgBox "Changed"
End Sub


Property Set MyComboBox(CMB As MSForms.ComboBox)
    Set cmbBox = CMB
End Property
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,959
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top