Excel VBA add handler to every checkbox in form

Question

I have an excel form with a large number of checkboxes that are added at runtime. I would like to add a handler to each one of those checkboxes that will run when the value is changed. I know in other versions of Visual Basic I would use AddHandler, but that doesn't work in Excel VBA.

Following an example, I came up with the following code:

'This is in a class module called CheckboxHandler
Public WithEvents cb As MSForms.CheckBox

Private Sub cb_change()
    MsgBox ("test")
end sub

And, in my userform, I have this code:

With CreateObject("Scripting.Dictionary")
.....'Unrelated code omitted

'Variable Checkboxes
'Add Handler to checkboxes
Dim colCBHandlers As Collection
Set colCBHandlers = New Collection
Dim objHandler As CheckboxHandler

Dim i As Long
Dim chkBox As MSForms.CheckBox
For i = 1 To .count - 1
    Set chkBox = Me.Controls.Add("Forms.Checkbox.1", "Checkbox" & i)
    chkBox.Caption = .Keys()(i)
    chkBox.VALUE = False
    chkBox.Top = (chkBox.Height + 10) * (i - 1) + 55
    chkBox.Left = 725
    Set objHandler = New CheckboxHandler
    Set objHandler.cb = chkBox
    colCBHandlers.Add objHandler
Next i
End With

Is this in inappropriate question? I'm confused about the downvoting. — ale10ander
– ale10ander, Commented Jan 5, 2015 at 21:56

Tim Williams · Accepted Answer · 2015-01-05 22:36:37Z

2

colCBHandlers will go out of scope as soon as the sub which creates the checkboxes exits.

You need to declare that collection as a global (at the module level) so it doesn't get lost once it has been created and populated.

edited Jan 5, 2015 at 22:36

answered Jan 5, 2015 at 22:31

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ale10ander Over a year ago

Thank you! I realized this an came back to update my question, only to find that you already had!

Collectives™ on Stack Overflow

Excel VBA add handler to every checkbox in form

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related