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: msdn.microsoft.com/en-us/library/… — Tim
– Tim, Commented Jul 25, 2016 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
– Ambie, Commented Jul 25, 2016 at 14:03
@user3598756 I stand corrected. when adding a MSForms.CheckBox, there is a click event: msdn.microsoft.com/en-us/library/office/…. It is a Windows.Forms checkbox that does not expse the click event. — Tim
– Tim, Commented Jul 25, 2016 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
– user3598756, Commented Jul 25, 2016 at 14:22

user3598756 · Accepted Answer · 2016-07-25 14:07:01Z

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.

Collectives™ on Stack Overflow

adding events on runtime for checkbox in userform in excel

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