CODEWITHSUNDEEP
excelvbacheckbox
ADS1979
ADS1979

Reputation: 9

How to add a select all check boxes option to a UserForm in Excel VBA

So I have this code

Dim LastRow     As Long

Private Sub Remove_Files_Button_Click()


On Error GoTo Error:
Dim i As Long
For i = 1 To LastRow
    If Me.Controls("CheckBox_" & i) Then
        Range("G" & i).Value = Range("A" & i).Value
    End If
    If Me.Controls("CheckBox_" & i) Then
        Range("H" & i).Value = Range("B" & i).Value
    End If
    If Me.Controls("CheckBox_" & i) Then
        Range("I" & i).Value = Range("C" & i).Value
    End If
Next i
    Range("A:F").Select
    Selection.Delete Shift:=xlToLeft
    Range("A:B").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo
Error:
    Unload Me
End Sub


Sub UserForm_Initialize()

Dim i           As Long
Dim chkBox      As MSForms.CheckBox

On Error GoTo Error:
LastRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To LastRow
    Set chkBox = Me.Controls.Add("Forms.CheckBox.1", "CheckBox_" & i)
    chkBox.Height = 30
    chkBox.Caption = Worksheets("Sheet1").Cells(i, 1).Value
    chkBox.Left = ((i - 1) Mod 4) * 100 + 5
    chkBox.Top = 5 + Fix((i - 1) / 4) * 30
Next i
Error:
End Sub

in a VBA form. and when I run it i generate this pop-up enter image description here

What I would like to do and haven't been able to figure out even how to go about it is have a second button that if clicked would automatically select all check boxes. Any help would be appreciated. Thanks

Upvotes: 0

Views: 1244

Answers (1)

Warcupine
Warcupine

Reputation: 4640

Iterate through the controls and check the name of the control. If like "checkbox*" then Value = True

Private Sub CommandButton1_Click()
    Dim ctl As Control
    For Each ctl In Me.Controls
        If ctl.Name Like "CheckBox*" Then
            ctl.Value = True
        End If
    Next ctl
End Sub

And if you need to change the value back, you can either make another button and just change ctl.Value = True to ctl.Value = False:

Private Sub CommandButton2_Click()
    Dim ctl As Control
    For Each ctl In Me.Controls
        If ctl.Name Like "CheckBox*" Then
            ctl.Value = False
        End If
    Next ctl
End Sub

Or add another if statement to invert the value, though if they check something it will uncheck and they won't all be equal.

Private Sub CommandButton1_Click()
    Dim ctl As Control
    For Each ctl In Me.Controls
        If ctl.Name Like "CheckBox*" Then
            If ctl.Value = False Then
                ctl.Value = True
            Else
                ctl.Value = False
            End If
        End If
    Next ctl
End Sub

enter image description here

Upvotes: 1

Related Questions