Dynamic Checkbox select all

Question

I have created a userform with a dynamic checkbox using the below code. I am struggling to figure out how to add a select all/unselect all checkbox at the bottom (or anywhere) since the list is dynamic. Any assistance will be appreciated.

Private Sub UserForm_Initialize()

'Create Checkboxes form with director names

Dim lRow        As Long
Dim i           As Long
Dim chkBox      As MSForms.CheckBox

lRow = Cells(Rows.Count, 2).End(xlUp).Row

Sheets("Directors_Database").Activate

For i = 4 To lRow

    Set chkBox = Me.Controls.Add("Forms.CheckBox.1", "CheckBox_" & i)
    chkBox.Caption = Worksheets("Directors_Database").Cells(i, 2).Value
    chkBox.Left = 5
    chkBox.Top = 5 + ((i - 2) * 20)
    chkBox.Width = 350

Next i

Set chkBox = Nothing

End Sub

score 1 · Accepted Answer · edited May 23 '17 at 11:54

1

You should create the "select all" checkbox at design time, not at runtime - else it getting a little tricky to assign a Event-Routine (see this discussion)

You could place the SelectAll-checkBox at the bottom by setting the top property (as you do already).

Then assign an Click-Event to that Checkbox:

Private Sub CheckBoxSelectAll_Click()

Dim c As Control

For Each c In Me.Controls
    If TypeOf c Is MSForms.CheckBox Then
        c.Value = CheckBoxSelectAll.Value
    End If
Next c

End Sub

edited May 23 '17 at 11:54

Community

1
1

answered May 22 '17 at 11:06

FunThomas

23,043
3
18
34

Yep this is what I started to try and do after the question. I just couldn't get the syntax on like 2 of the for statement right lol. Thanks FunThomas! – KoderM16 May 22 '17 at 11:20

Dynamic Checkbox select all

1 Answers1