How to loop checkboxes?

Question

I have 20 checkboxes named checkbox1 to checkbox20.

I would like to record the username in column B when each checkbox is checked.

For example, when a user clicks on checkbox1, B1 would populate the username, if checkbox2 is clicked, B2 would populate username.

I know how to write code for each checkbox and copy it 20 times. How can I combine them into one sub?

Private Sub CheckBox1_Click()

Dim i As Integer

For i = 1 To 20

    If Me.CheckBox & i = True Then

        Range("b1" & i).Value = Environ("USERNAME")

    Else: Range("b1" & i).Value = ""

    End If

Next

End Sub

You may create wrapper class and declare Public WithEvents ctl As MSForms.CheckBox and create Private Sub ctl_Click() or Private Sub ctl_Change() which will handle checkbox event, then create 20 instances of the class and assign each checkbox to each instance within loop. Note you should use Form Controls Check Boxes. — omegastripes
– omegastripes, Commented Jan 5, 2020 at 2:43

Harun24hr · Accepted Answer · 2020-01-05 02:05:23Z

0

In a module write following sub and then call this sub from CheckBox_Click event.

Sub LoopCheckBoxes()
Dim i As Long
    For i = 1 To 20
        If ActiveSheet.Shapes("Check Box " & i).OLEFormat.Object.Value = 1 Then
            Range("B" & i) = Environ("USERNAME")
        End If
    Next i
End Sub

See below screenshot to call above sub from checkbox click event.

answered Jan 5, 2020 at 2:05

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

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

Collectives™ on Stack Overflow

How to loop checkboxes?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related