Individual click events to dynamic checkboxes on userform, VBA

[Ramachandran]

I am populating a frame on a form with a number of checkboxes:

Userform_initialize()
...

Dim cB As MSForms.CheckBox

For i = 1 To dX
For j = 1 To dY

idx = j + (((i - 1) *dY))

Set cB = Frame2.Controls.Add("Forms.CheckBox.1", "chkBox" & idx)
With cB
.width = 50
.Height = 16
.Left = 6 + (54 * (j - 1))
.Top = 17 + (19 * (i - 1))
.Visible = True
End With

Next j
Next i

The frame is populated fine.
However I want individual click events for each of the chkBox'es.

Best case:
One clever multi event handler

private sub chkBoxClick(boxNumber as integer)
'Do stuff
end sub

or perhaps use some userform event:

private sub Userform_Clack()
If Left(me.activecontrol.name, 6).value = "chkBox' then...
end sub

Worst case:
I could create thousands single event handlers.

private sub chkBox173_Click()
Call LotsOfFun(173)
end sub

I can't even find a way to this...

Any suggestions?

 

[Darren Bartrup]

I've had to do this before (with combo boxes, but a check box will be the same) and you need to create a class module and use WithEvents.

Some of the help I had from this forum is here:
http://www.mrexcel.com/forum/excel-questions/212171-afterupdate-event-combo-box.html
http://www.mrexcel.com/forum/excel-questions/224392-click-event-not-working-solved.html

Chip Pearson has some stuff as well:
Events In Excel VBA

 

[Ramachandran]

.

 

[Ramachandran]

Thank you very much sir!
Pairing all of the checkboxes with a class that uses WithEvents didn't help much at first - only the last created checkbox had the desired event.
However when I created a collection and added every instances of the class in that collection, it all worked

class - clsBoxEvent:

Option Explicit
Public WithEvents cBox As MSForms.CheckBox

Private Sub cBox_Click()

MsgBox cBox.name​

End Sub

userform:

Dim chkBoxEvent As clsBoxEvent
Dim chkBox As MSForms.CheckBox
Dim chkBoxColl As Collection


Private Sub Userform_Initialize()

For i = 1 To dX​

For j = 1 To dY
​

idx = j + (((i - 1) * dY))
​

Set chkBox = Frame2.Controls.Add("Forms.CheckBox.1", "ChkBox" & idx)
​

With chkBox
​

...
​

End With
​

Set chkBoxEvent = New clsBoxEvent
​

Set chkBoxEvent.cBox = Me.Controls(chkBox.name)
​

chkBoxColl.Add chkBoxEvent
​

Next j
​

Next i
​

 

[Darren Bartrup]

Think that's the problem I had at first - getting it all into a collection. Glad it worked though.