Creating checkboxes in a UserForm using Excel VBA.

Steps to create checkboxes in a UserForm in Excel VBA

1. Open the VBA Editor
   • In Excel, press Alt + F11 to open the VBA editor.
2. Add a UserForm
   • Click on Insert > UserForm to add a new form.
3. Add Checkboxes
   • Click on the « Checkbox » control in the toolbox of the UserForm and place it on the form. You can add multiple checkboxes according to your needs.
4. Write the VBA Code
   • After adding checkboxes to your form, you will write the code to handle the events associated with them, such as checking which checkboxes are selected and performing actions based on the user’s choices.

Detailed Code to Create a UserForm with Checkboxes

Let’s assume you have three options represented by checkboxes: « Option 1 », « Option 2 », and « Option 3 ». The goal is to capture which options are selected and display them in an Excel cell.

1.1 Creating Checkboxes Dynamically in the Code

We will dynamically create a set of checkboxes and add them to the form using VBA code.

Private Sub UserForm_Initialize()
    Dim i As Integer
    Dim chkBox As MSForms.CheckBox
    Dim options() As String
    options = Array("Option 1", "Option 2", "Option 3") ' Array with the option labels   
    ' Loop to create checkboxes dynamically
    For i = 0 To UBound(options)
        Set chkBox = Me.Controls.Add("Forms.CheckBox.1") ' Create a checkbox
        chkBox.Caption = options(i) ' Assign text to the checkbox
        chkBox.Left = 10 ' Horizontal position
        chkBox.Top = 10 + (i * 25) ' Vertical position (25px spacing between checkboxes)
        chkBox.Name = "chkOption" & i ' Name each checkbox (chkOption0, chkOption1, etc.)
    Next i
End Sub

Explanation of the Code:

• UserForm_Initialize(): This procedure runs when the form is opened. It initializes the checkboxes.
• Me.Controls.Add(« Forms.CheckBox.1 »): This line creates a checkbox dynamically.
• chkBox.Caption = options(i): The text of the checkbox is set from the options array.
• chkBox.Left and chkBox.Top: These properties define the position of each checkbox on the form.
• chkBox.Name = « chkOption » & i: The name of each checkbox is dynamic and based on the index i.

1.2 Retrieve Selected Checkboxes

When the user clicks a submit button, we want to retrieve which checkboxes were selected and display them in an Excel cell.

Here is the code for the submit button:

Private Sub btnSubmit_Click()
    Dim i As Integer
    Dim selectedOptions As String
    selectedOptions = "Selected options: "   
    ' Loop through the checkboxes
    For i = 0 To 2 ' 3 options (index 0, 1, 2)
        If Me.Controls("chkOption" & i).Value = True Then
            selectedOptions = selectedOptions & Me.Controls("chkOption" & i).Caption & ", "
        End If
    Next i   
    ' Remove the last comma and space
    If Len(selectedOptions) > 0 Then
        selectedOptions = Left(selectedOptions, Len(selectedOptions) - 2)
    End If   
    ' Display the selected options in an Excel cell
    Sheets("Sheet1").Range("A1").Value = selectedOptions
    MsgBox selectedOptions ' Show a message with the selected options
End Sub

Explanation of the Code:

• If Me.Controls(« chkOption » & i).Value = True Then: This condition checks if the checkbox is selected (True means checked, False means unchecked).
• selectedOptions = selectedOptions & Me.Controls(« chkOption » & i).Caption & « , « : If the checkbox is checked, its caption (label) is appended to the selectedOptions string.
• Sheets(« Sheet1 »).Range(« A1 »).Value = selectedOptions: The selected options are displayed in cell A1 on the worksheet.
• MsgBox selectedOptions: A message box shows the selected options.

Add a Close Button to Close the UserForm

You can also add a button to the form to close it once the user is done.

Private Sub btnClose_Click()
    Unload Me ' Close the UserForm
End Sub

Add a Button to Launch the UserForm

In a standard module, you can add code to open the UserForm. For example, you can create a button on the Excel worksheet to show the form:

Sub ShowUserForm()
    UserForm1.Show
End Sub

Summary:

• The code creates a UserForm with dynamically generated checkboxes based on an array of options.
• When the user submits their choices, the code checks which checkboxes are selected and displays the results in an Excel cell.
• You can add a close button to cleanly close the UserForm when done.