Dynamic checkbox events through commandbutton

Question

I am currently programming a sheet which visualizes data sets in graphs. Because the user of this sheet will not need all the graphs, I would like to let them choose the ones needed through a UserForm. Since the amount of data sets is variable, the UserForm will have the same amount of checkboxes as there are datasets.

The Userform code is as follows.

Private Sub UserForm_Initialize()

Dim chkBoxA     As MSForms.CheckBox
Dim chkBoxB     As MSForms.CheckBox
Dim lblBox      As MSForms.Label
Dim cnt         As Control

Amount = Sheet4.Range("C4").Value 'Amount of datasets

For i = 1 To Amount
    Set lblBox = Me.Controls.Add("Forms.label.1", "Label" & i)
        lblBox.Caption = "Set" & i
        lblBox.Left = 5
        lblBox.Top = 8 + ((i - 1) * 40)
    Set chkBoxA = Me.Controls.Add("Forms.CheckBox.1", "A" & i)
        chkBoxA.Caption = "Graph a"
        chkBoxA.Left = 55
        chkBoxA.Top = 5 + ((i - 1) * 40)
    Set chkBoxB = Me.Controls.Add("Forms.CheckBox.1", "B" & i)
        chkBoxB.Caption = "Graph b"
        chkBoxB.Left = 55
        chkBoxB.Top = 20 + ((i - 1) * 40)
Next

CommandButton1.Left = 20
CommandButton1.Top = 40 + ((Amount - 1) * 40)
CommandButton1.TabIndex = Amount * 3 + 1

Me.Height = 220

Me.ScrollBars = fmScrollBarsVertical
Me.ScrollWidth = Me.InsideWidth * 9
For Each cnt In Me.Controls
    If cnt.Top + cnt.Height > Me.ScrollHeight Then
        Me.ScrollHeight = cnt.Top + cnt.Height + 5
    End If
Next

End Sub

When the UserForm is filled in (graphs are chosen by clicking on the options), the user will press CommandButton1. An event should then be run to show the correct graph, but for the simplicity I am first testing if a MsgBox will show up. Unfortunately the MsgBox does not show up.

Private Sub CommandButton1_Click()
'Will fix this with a loop
If A1 = True Then
    MsgBox ("TestA1")
End If
If B1 = True then
    MsgBox ("TestB1")
End If
If A2 = True then
    MsgBox ("TestA2")
End If

Unload Me

End Sub

I am stuck on this part. The checkboxes do show up on the UserForm and they are clickable, but the commandbutton only shuts down the sub (Unload Me). I would like to see the MsgBox show up when I select the corresponding option and click the commandbutton. Any help on getting this to work is appreciated!

Jzz · Accepted Answer · 2015-12-11 15:41:05Z

1

You are referencing 'A1' in the sub, but that variable does not exitst at compile time, because you add them dynamically. What you need to do is loop the controls, to check the names. Best practice is to put the checkboxes in a frame, to be able to group them. Add a frame to the userform and name it 'checkboxframe'

And then instead of:

For i = 1 To Amount
    Set lblBox = Me.Controls.Add("Forms.label.1", "Label" & i)
        lblBox.Caption = "Set" & i
        lblBox.Left = 5
        lblBox.Top = 8 + ((i - 1) * 40)
    Set chkBoxA = Me.Controls.Add("Forms.CheckBox.1", "A" & i)
        chkBoxA.Caption = "Graph a"
        chkBoxA.Left = 55
        chkBoxA.Top = 5 + ((i - 1) * 40)
    Set chkBoxB = Me.Controls.Add("Forms.CheckBox.1", "B" & i)
        chkBoxB.Caption = "Graph b"
        chkBoxB.Left = 55
        chkBoxB.Top = 20 + ((i - 1) * 40)
Next

you would need to do:

With Me.checkboxframe
    For i = 1 To Amount
        Set lblBox = .Controls.Add("Forms.label.1", "Label" & i)
            lblBox.Caption = "Set" & i
            lblBox.Left = 5
            lblBox.Top = 8 + ((i - 1) * 40)
        Set chkBoxA = .Controls.Add("Forms.CheckBox.1", "A" & i)
            chkBoxA.Caption = "Graph a"
            chkBoxA.Left = 55
            chkBoxA.Top = 5 + ((i - 1) * 40)
        Set chkBoxB = .Controls.Add("Forms.CheckBox.1", "B" & i)
            chkBoxB.Caption = "Graph b"
            chkBoxB.Left = 55
            chkBoxB.Top = 20 + ((i - 1) * 40)
    Next
End With

And to add the checkboxes to the frame, use something like:

For Each ctr In UserForm1.frame("checkboxframe").Controls
    If TypeName(ctr) = "CheckBox" Then
        If ctr.Value = True Then
            'do something usefull here
            msgbox ctr.name
        End If
    End If
Next ctr

edited Dec 11, 2015 at 15:41

answered Dec 11, 2015 at 15:25

Jzz

7394 silver badges8 bronze badges

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

2 Comments

Peleus Over a year ago

Thank you for your answer! Unfortunately I am getting an error on the UserForm1.Frame("checkboxes").Controls: "Compile error: Method or data member not found".

Jzz Over a year ago

Yes, in my pseudo code I used the frame I was talking about. You need to add it to the Userform first, and add your checkboxes to that frame. Give me a minute to edit my answer for you

JDB_Dragon · Accepted Answer · 2015-12-11 15:30:15Z

0

The reason nothing appears is because there is no object "A1" manually defined as a variable.

To get the value of the box you Dynamically named "A1" you would have to refer to it as such:

If Me.Controls.Item("A1").Value = True then

Hope this helps!

answered Dec 11, 2015 at 15:30

JDB_Dragon

16210 bronze badges

1 Comment

Peleus Over a year ago

Perfect! Simple and exactly what I was looking for, thank you!

Collectives™ on Stack Overflow

Dynamic checkbox events through commandbutton

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related