Add CheckBox on Worksheet or UserForm using VBA

We can add CheckBox on Worksheet or UserForm is using VBA with checkbox control and is used to specify or indicate boolean choice. In this section we will see how to add single checkbox or multiple checkboxes on the worksheet or userform using VBA. Please find the more details about add checkbox control using VBA in the following chapter.

In this Topic:

• How to add a CheckBox on the Worksheet Using VBA Code
• Example 1: Add Form Check Box Control
• Example 2: Add ActiveX Check Box Control
• How to add multiple CheckBoxs on the Worksheet using VBA code
• Example 1: Add Form Check Box Control
• Example 2: Add ActiveX Check Box Control

Add a CheckBox on the Worksheet Using VBA Code

Please find the below two example macros, it will show you how to add checkbox on the Worksheet using VBA code.

1. Click Developer Tab in the Excel menu bar.
2. On the Code group, Click Visual Basic. Now, VBA Editor window will be displayed.
3. Click Insert from the menu, Click Module and then add the below example macros into the module.

Example 1: Add Form Check Box Control

The below example show you how to add form checkbox control on the worksheet.

Private Sub ActX_Add_CheckBox_Ex1()
    Sheets("Sheet3").CheckBoxes.Add(Left:=Range("E1").Left, Top:=Range("E1").Top, Width:=Range("G1").Width, Height:=Range("E1").Height).Select
    With Selection
        .Caption = "Adding Checkbox"
    End With
End Sub

Example 2: Add ActiveX Check Box Control

The below example show you how to add ActiveX checkbox control on the worksheet.

Private Sub ActX_Add_CheckBox_Ex2()
    Sheets("Sheet3").OLEObjects.Add "Forms.CheckBox.1", Left:=Range("A1").Left, Top:=Range("A1").Top, Width:=Range("A1").Width, Height:=Range("A1").Height
End Sub

4. Click run button or ‘F5’ to execute the above macro code.
5. Go to Sheet3 to see the output for the above mentioned examples.

Add multiple CheckBoxs on the Worksheet using VBA code

Please find the below two example macros, it will show you how to add multiple checkboxs on the Worksheet using VBA code.

1. Click Developer Tab in the Excel menu bar.
2. On the Code group, Click Visual Basic. Now, It will display VBA Editor window.
3. Click Insert from the menu, Click Module and then add the below example codes into the module.

Example 1: Add Form Check Box Control

The below example show you how to add multiple form checkbox control on the worksheet.

Sub ActX_Add_Multiple_CheckBox_Ex1()

'Disable Screen Update
Application.ScreenUpdating = False

'Variable Declaration
Dim Rng As Range
Dim ShtRng As Range
Dim WrkSht As Worksheet
Dim i As Integer

'Variable Initialization
i = 1

Set ShtRng = Application.Selection
Set ShtRng = Application.InputBox("Range", "Analysistabs", ShtRng.Address, Type:=8)
Set WrkSht = Sheets(“Sheet3”)


For Each Rng In ShtRng
    With WrkSht.CheckBoxes.Add(Left:=Rng.Left, Top:=Rng.Top, Width:=Rng.Width, Height:=Rng.Height).Select
        With Selection
            .Characters.Text = Rng.Value
            .Caption = ""
            .Caption = "Check Box " & i
            i = i + 1
        End With
    End With
Next

ShtRng.ClearContents
ShtRng.Select

'Enable Screen Update
Application.ScreenUpdating = True

End Sub

Example 2: Add ActiveX Check Box Control

The below example show you how to add multiple ActiveX checkbox control on the worksheet.

Sub ActX_Add_Multiple_CheckBox_Ex2()

'Disable Screen Update
Application.ScreenUpdating = False

'Variable Declaration
Dim Rng As Range
Dim ShtRng As Range
Dim WrkSht As Worksheet


Set ShtRng = Application.Selection
Set ShtRng = Application.InputBox("Range", "Analysistabs", ShtRng.Address, Type:=8)
Set WrkSht = Application.ActiveSheet

For Each Rng In ShtRng
    WrkSht.OLEObjects.Add "Forms.CheckBox.1", Left:=Rng.Left, Top:=Rng.Top, Width:=Rng.Width, Height:=Rng.Height
Next

ShtRng.ClearContents
ShtRng.Select

'Enable Screen Update
Application.ScreenUpdating = True

End Sub

4. Click run button or ‘F5’ to execute the above macro code.
5. Go to active sheet to see the output for the above mentioned examples.