CODEWITHSUNDEEP
excelvbacheckboxuserform
KoderM16
KoderM16

Reputation: 157

Dynamic Checkbox select all

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

Upvotes: 0

Views: 392

Answers (1)

FunThomas
FunThomas

Reputation: 29146

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

Upvotes: 1

Related Questions