Checkbox event doesn't work in VBA

Question

I dynamically defined checkboxes, which are added to the userform on runtime.

Sub Userform_Initialize()
  For i = 1 To 4
    Set Chk = Frame4.Controls.Add("Forms.Checkbox.1", "Checkbox" & i)
    With Chk
       .top = 84 + k
       .left = 336
       .Width = 100
       .Height = 18
       .Caption = "Add item"
       .Visible = True
    End With
    k = k + 24
  Next i
End Sub

Public WithEvents Chk As MSForms.CheckBox
Private Sub Chk_Click()
    MsgBox Chk
End Sub

For some reason, the event doesn't respond. Does anyone have an idea as to why? I need the boolean of the checkbox. That means when the user clicked on a dynamic checkbox, then I see "True" returned in the msgbox.

You need to create a class to handle the events - check these links: http://stackoverflow.com/questions/3014421/how-to-add-events-to-controls-created-at-runtime-in-excel-with-vba (use the class event, not adding extra code!). — Darren Bartrup-Cook, Jul 20 '16 at 09:42
@DarrenBartrup-Cook Thanks for your post. I have it up-voted :) I will share my solution code soon. — yuro, Jul 20 '16 at 09:52

score 1 · Accepted Answer · answered Jul 20 '16 at 10:28

Add a class module to your project, name it as you want (say "ChkBox"), and put this code in it:

Public WithEvents Chk As MSForms.CheckBox

Private Sub Chk_Click()
    MsgBox Chk.Value
End Sub

In your userform code, add this

Dim myChks(1 To 4) As New ChkBox '<--| this must be at the very top of your userform code pane

Sub Userform_Initialize()
    Dim i As Long, k As Long

    With Me.Frame4.Controls
        For i = 1 To 4
            With .Add("Forms.Checkbox.1", "Checkbox" & i)
                .Top = 84 + k
                .Left = 336
                .Width = 100
                .Height = 18
                .Caption = "Add item"
                .Visible = True
                Set myChks(i).Chk = .Parent.Item(.Name)
            End With
            k = k + 24
        Next i
    End With
End Sub

Checkbox event doesn't work in VBA

1 Answers1