Writing code that applies to multiple check boxes in VBA

Question

I am trying to write some code for a VBA userform that has about 100 checkboxes. I was wondering if there is a way that I could have one piece of code that applies to any checkbox or if I have to write 100 seperate functions for checkbox1_click, checkbox2_click, checkbox3_click, etc.

Thanks for any help in advance :)

edit: I realized that it would help to explain exactly what I am trying to do. There will be 100 check boxes and whenever one is clicked I would like to do this:

Call CheckBoxClicked("checkboxname")

as you question stands the answer is you can have: one piece of code — user2140173
– user2140173, Commented Aug 13, 2013 at 13:56

user857521 · Accepted Answer · 2013-08-13 14:10:34Z

2

Put this in a class moduled named clsCheckBoxHandler

Public WithEvents chk As MSForms.CheckBox

Private Sub chk_Click()
    MsgBox chk.Caption & " Clicked!"
End Sub

then in the Userform

Dim chkCollection As Collection


Private Sub UserForm_Initialize()
Dim cCont As Control
Dim chkH As clsCheckBoxHandler

    Set chkCollection = New Collection

    For Each cCont In Me.Controls

        If TypeName(cCont) = "CheckBox" Then

            Set chkH = New clsCheckBoxHandler
            Set chkH.chk = cCont
            chkCollection.Add chkH

        End If

    Next cCont

End Sub

this is just a simple handler for checkboxes that has a click event but can be extended to cover multiple controls and events.

answered Aug 13, 2013 at 14:10

user857521

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

2 Comments

user2678840 Over a year ago

Thanks so much for the help. If its not too much to ask, could you show me how I would write this program so that when a checkbox is clicked, it runs a sub passing the checkbox name as a string. Basically I want every time a checkbox clicked it to do Call CheckBoxClicked("checkboxname").

user2678840 Over a year ago

I actually figured it out. Thanks so much :)

Collectives™ on Stack Overflow

Writing code that applies to multiple check boxes in VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related