1

I am trying to write code that will handle the "change" event of any ComboBox on my userform.

I went by this question's answer and created a separate class, etc.

However, it doesn't work. Using the same code on a new project works fine, this code on this project works sometimes when I use the "step by step" execution (F8), but when using normal running it doesn't.

Looking at other people's similar predicaments I added "DoEvents" but that didn't help.

This is my code in the UserForm

Private Sub UserForm_Initialize()

Dim ComboBox_Collection As Collection
Dim ctrl As Control
Dim cbc As ComboBox_Class

Set ComboBox_Collection = New Collection
    For Each ctrl In UserForm1.MultiPage.Pages(2).Controls
    DoEvents
        If TypeName(ctrl) = "ComboBox" Then
            DoEvents
            Set cbc = New ComboBox_Class
            Set cbc.Control = ctrl
            ComboBox_Collection.Add cbc
            DoEvents
        End If
    Next ctrl
Set cbc = Nothing

End Sub

And the class module, named "ComboBox_Class":

Private WithEvents TriggerComboBox As MSForms.ComboBox

Public Property Set Control(CB As MSForms.ComboBox)
    Set TriggerComboBox = CB
End Property

Private Sub TriggerComboBox_Change()
    MsgBox ("yay")
End Sub
Community
  • 1
  • 1
mathgenius
  • 503
  • 1
  • 6
  • 21

1 Answers1

2

ComboBox_Collection needs to be a Global variable (at least, Global to your form's code module), or it will disappear/go out of scope as soon as your UserForm_Initialize Sub is finished executing

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Thanks! That was it, I moved the collection to the Module and made it public, now it works! – mathgenius Mar 18 '16 at 18:56
  • @mathgenius You don't need to make it `Public` unless you need to access it from outside the class. And even if you need to access it outside the class, you should be exposing *a copy of it* (an array perhaps) as a `Public Property Get` member, to preserve proper encapsulation. – Mathieu Guindon Mar 18 '16 at 19:40