[VBA] How to Use Checkboxes in a UserForm (Checking On/Off Status)

Introduction

When you want to allow users to freely select “necessary items” from multiple options in a VBA UserForm, the Checkbox control is the best choice. It is useful for various purposes, such as questionnaire answers or selecting options to apply.

You can easily determine whether a user has checked a box by checking the .Value property of the checkbox.

This article explains how to retrieve the status (On/Off) of multiple checkboxes placed on a UserForm and programmatically determine which items were selected.

VBA Sample Code: Getting Checkbox Status

This macro checks the status of three checkboxes when the “Confirm” button on the UserForm is clicked and displays a list of selected items in a message box.

UserForm Code

' Processing when the "Confirm" button (ConfirmButton) is clicked
Private Sub ConfirmButton_Click()

    '== Define variables ==
    Dim selectedOptions As String
    Dim i As Long
    Dim targetCheckbox As MSForms.CheckBox
    
    selectedOptions = "Selected Options:" & vbCrLf
    
    '== Loop through checkboxes 1 to 3 ==
    For i = 1 To 3
        ' Use the Controls collection to specify the checkbox by name
        Set targetCheckbox = Me.Controls("OptionCheckBox" & i)
        
        ' Determine if the check is On (True) or Off (False) using the .Value property
        If targetCheckbox.Value = True Then
            ' If checked, add the Caption (display text) to the result
            selectedOptions = selectedOptions & "- " & targetCheckbox.Caption & vbCrLf
        End If
    Next i
    
    '== Display the result in a message box ==
    MsgBox selectedOptions, vbInformation, "Selection Result"

End Sub

Preparation Before Execution

1. Create a UserForm.
2. Place 3 Checkboxes and 1 Command Button on the form.
3. Change the (Name) property of the three checkboxes to OptionCheckBox1, OptionCheckBox2, and OptionCheckBox3 in order from top to bottom. Set their Caption properties to something like “Notify by Email”, “Notify by SMS”, and “Notify by Phone”.
4. Change the (Name) of the command button to ConfirmButton and its Caption to Confirm.
5. Paste the code above into the form’s code module, then display and use the form.

Explanation of the Code

Set targetCheckbox = Me.Controls("OptionCheckBox" & i)

We use a For loop to process the controls named OptionCheckBox1, OptionCheckBox2, and OptionCheckBox3 in order.

• Me.Controls(...): This collection allows you to access controls on the form by specifying their name (as a string). By using the loop counter i to create the string "OptionCheckBox" & i, you can handle multiple controls efficiently.

If targetCheckbox.Value = True Then

This line is the core part of determining the checkbox status.

• .Value: The Value property of a checkbox returns True if it is checked (On) and False if it is not (Off).

The code inside the If statement is executed only when Value is True, meaning “the user selected that item.”

selectedOptions = selectedOptions & ...

If the check is On, the code retrieves the .Caption property (the text displayed on the screen, e.g., “Notify by Email”) of that checkbox and adds it to the string variable selectedOptions that stores the result.

Summary

In this article, we explained how to determine whether a checkbox in a UserForm is selected using VBA.

• Check the status using the .Value property (On = True, Off = False).
• Multiple controls can be processed efficiently by combining the Controls collection and a loop.

By applying this basic logic, you can create more interactive and multi-functional tools, such as branching processing or changing calculation results based on the options selected by the user.