adding events on runtime for checkbox in userform in excel

Question

I have tried @DaveShaw code, for events on runtime for checkboxes, is click not an valid method for checkbox? It never get into the method checkBoxEvent_click

Dim CheckBoxArray() As New ClassEvents          
for i=0 to 10  
          Set cTemp = MOM.Frame_MOM_MOM.Controls.Add("Forms.CheckBox.1")

            With cTemp
                .Top = HeaderOffset + RowOffset + i * 25 'your top pos
                .Visible = True
                .Left = 30  'your left pos
                .Width = widthOfLabel 'your width
                .Name = Replace(keyArrays(i, 1), " ", "_")
                .Caption = keyArrays(i, 1) 'your caption ,


            End With

            ReDim Preserve CheckBoxArray(0 To i)
            Set CheckBoxArray(i).checkBoxEvent = cTemp
            next i

and my ClassEvents class looks like this:

Public WithEvents checkBoxEvent As MSForms.checkBox

Private Sub checkBoxEvent_click()
    MsgBox "halla" 'checkBox.Caption
End Sub

"is click not an valid method for checkbox?" Correct: https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.controls.checkbox.aspx?f=255&MSPPError=-2147217396 — Tim, Jul 25 '16 at 13:37
Theoretically, your code will work ... could you put `Option Explicit` at the top of your module and remove any `On Error Resume Next` lines if you have them. — Ambie, Jul 25 '16 at 14:03
@user3598756 I stand corrected. when adding a MSForms.CheckBox, there is a click event: https://msdn.microsoft.com/en-us/library/office/gg251734%28v=office.15%29.aspx?f=255&MSPPError=-2147217396. It is a Windows.Forms checkbox that does not expse the click event. — Tim, Jul 25 '16 at 14:11
@Ambie _"Theoretically, your code will work"_ provided that `CheckBoxArray()` is a Userfom wide scoped variable, thus to be placed at the very top of the Userfom code pane and so outside any Subs/Function code — user3598756, Jul 25 '16 at 14:22
@user3598756 that's not necessarily correct. It all depends on where the OP is showing his UserForm and whether it is declared modally. If he placed `MOM.Show` in the same procedure as his Module code then it will work fine. It's a good point though because we do need to know how and where the UserForm is being shown. — Ambie, Jul 25 '16 at 14:29
@Ambie, I never met those conditions you explained so I'm taking it from you that it's a possible "working" environment. Anyhow let's see skatun feedback to my answer — user3598756, Jul 25 '16 at 14:36
@user3598756 I think I'm right in saying that the Userform must be shown and remain within the scope of the declared class. Certainly his code worked for me. For a more structured solution, though, your answer is much more appropriate. — Ambie, Jul 25 '16 at 14:40
@user3598756 It worked because the MOM.show was in the same procedure, but as you suggested I have now moved the collection of ClassEvents to my settings module, a module where I keep all my global variables. What I am trying todo is to build up a string in the order that checkboxes where clicked. — skatun, Jul 26 '16 at 07:48

score 1 · Accepted Answer · answered Jul 25 '16 at 14:07

you have to keep Dim CheckBoxArray() As New ClassEvents at the very top of your userfom code pane, thus outside any of its subs/functions

furthermore use Option Explicit statement too

it becomes

Option Explicit

Dim CheckBoxArray() As New ClassEvents '<--| keep this line at the very top of your userform code pane

Private Sub UserForm_Initialize()
    Dim i As Long
    Dim cTemp As MSForms.CheckBox '<-- with "Option Explicit" you have to declare all your variables

    For i = 0 To 10
        Set cTemp = MOM.Frame_MOM_MOM.Controls.Add("Forms.CheckBox.1")
          With cTemp
             .Top = HeaderOffset + RowOffset + i * 25 'your top pos
              .Visible = True
              .Left = 30  'your left pos
              .Width = widthOfLabel 'your width
              .Name = Replace(keyArrays(i, 1), " ", "_")
              .Caption = keyArrays(i, 1) 'your caption ,
          End With
          ReDim Preserve CheckBoxArray(0 To i)
          Set CheckBoxArray(i).checkBoxEvent = cTemp
    Next i
End Sub

furthermore, since you already know the dimension of your array, Dim it at the beginning and don't ReDim it at every iteration:

Option Explicit

Dim CheckBoxArray(0 To 10) As New ClassEvents '<--| keep this line at the very top of your userform code pane

Private Sub UserForm_Initialize()
    Dim i As Long
    Dim cTemp As MSForms.CheckBox '<-- with "Option Explicit" you have to declare all your variables

    For i = 0 To 10
        Set cTemp = MOM.Frame_MOM_MOM.Controls.Add("Forms.CheckBox.1")
          With cTemp
             .Top = HeaderOffset + RowOffset + i * 25 'your top pos
              .Visible = True
              .Left = 30  'your left pos
              .Width = widthOfLabel 'your width
              .Name = Replace(keyArrays(i, 1), " ", "_")
              .Caption = keyArrays(i, 1) 'your caption ,
          End With
          Set CheckBoxArray(i).checkBoxEvent = cTemp
    Next i
End Sub

So, making it explicit helped. I do not know the size of the array(input is coming from DB, i stripped the code). What I want to achieve is to find out which order the checkboxes where clicked in, then make a string with the name of the checkboxes in order of click appearance. I will put `Dim CheckBoxArray(0 To 10) As New ClassEvents` in my setting class where I keep all my global variables. — skatun, Jul 25 '16 at 15:19

adding events on runtime for checkbox in userform in excel

1 Answers1