Add CheckBox on Worksheet or UserForm using VBA

We can Insert Checkbox in Excel Ranges, Worksheets or Userforms using VBA. Add Checkbox control to specify or indicate Boolean choice and take the decisions. In this section we will see how to add single checkbox or multiple checkboxes on worksheets or Userforms using VBA. Please find more details about add checkbox control using VBA in the following chapter.

In this Topic:

• How to Insert Checkbox in Excel
• 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
• Add checkbox in Excel to Selected Range Using VBA
  • Add Form Check Box Control to Selected Range
  • Add ActiveX Check Box Control to Selected Range
• How to add multiple CheckBoxes on the Worksheet using VBA code
  • Example 1: Add Form Check Box Control
  • Example 2: Add ActiveX Check Box Control

How to Insert Checkbox in Excel

Inserting checkboxes in Excel can help you create interactive to-do lists, manage a project’s tasks, or make survey forms. Here’s a step-by-step guide on how to insert a checkbox in Excel:

1. Enable the Developer Tab:

Before you can add a checkbox, you’ll need to have the Developer tab visible in the ribbon.

• For Excel 2016, 2019, and Microsoft 365:
  1. Go to the File menu and choose Options.
  2. In the Excel Options window, select Customize Ribbon.
  3. Check the Developer option in the right column.
  4. Click OK.
• For Excel 2010 and 2013:
  1. Right-click anywhere on the ribbon and choose Customize the Ribbon.
  2. Check the Developer option and click OK.

2. Inserting a Checkbox:

1. Select the cell or the area where you want to add checkboxes.
2. Go to the Developer tab in the ribbon.
3. In the Controls group, click on the Insert button.
4. Under Form Controls, select the Checkbox option. Your cursor will change to a crosshair.
5. Click where you want to add the checkbox. You can drag and resize if needed.

3. Formatting the Checkbox:

1. Right-click on the checkbox and select Edit Text to label it as per your requirement.
2. For additional formatting options, right-click the checkbox and choose Format Control. Here, you can adjust the control’s properties, including its value, cell link, and more.

4. Copying the Checkbox to Other Cells:

1. Select the cell containing the checkbox.
2. Hover your cursor over the bottom right corner of the cell until it changes to a small black cross.
3. Drag to fill the desired range, and checkboxes will be copied to the selected cells.

5. Linking the Checkbox to a Cell:

This allows you to monitor the checkbox’s status (checked or unchecked) using a TRUE or FALSE value in a different cell.

1. Right-click on the checkbox and select Format Control.
2. Go to the Control tab.
3. In the Cell link box, select a cell that you want to link. Now, when you check or uncheck the box, the linked cell will show TRUE or FALSE respectively.

6. Deleting the Checkbox:

If you need to remove a checkbox, simply select it and press the Delete key.

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. We use CheckBoxes.Add method to insert Form Check box controls in the Worksheets and .OLEObjects.Add “Forms.CheckBox.1” Method to add ActiveX controls in the Workseets and Userforms.

1. 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

Here CheckBoxes.Add method will add the ick box to the given cell in a specific worksheet. We can use ActiveSheet.CheckBoxes.Add method to add the control in the ActiveSheet.

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

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

Add checkbox in Excel to Selected Range using VBA

Adding checkboxes to a range of cells using VBA can save considerable time, especially when you wants to add to a Range of Cells. Here’s a very handy VBA code for adding checkboxes to a selected range in Excel with the help of VBA.

If you want to add Check Boxes in Excel, you can do so differently depending on the type of checkbox you’re working with: Form Control checkboxes or ActiveX Control checkboxes.

Form Control Checkboxes

For Control Check Boxes can be inserted to in to worksheet and linked to a range. It will result True, False when you click the Check Box. It will not required VBA to function, you can use the Excel Formula to determine if the Check Box is checked or not.

Sub sbAT_AddCheckboxesToRangeToSelectedRange()

    Dim rng As Range
    Dim cell As Range
    Dim chkBox As CheckBox

    ' Set the range where you want checkboxes
    Set rng = Selection 'Range("A1:A10") 'Modify the sheet name and range accordingly

    ' Loop through each cell in the range
    For Each cell In rng
        ' Add a checkbox in the cell
        Set chkBox = cell.Parent.CheckBoxes.Add(cell.Left, cell.Top, cell.Width, cell.Height)
        chkBox.Text = ""  ' Remove the default text (if any)
        chkBox.LinkedCell = cell.Address  ' Link checkbox status to the cell
    Next cell

End Sub

This procedure will add the Worksheet form control to a Selected Range, You can specify any required range based on your needs, for example (Range “A1:A10”). This also removed the default text of the Check Box and Linked to the respective Cell. This will be very useful if you wants to add multiple check boxes to a selected range.

ActiveX Control Checkboxes

ActiveX Check Boxes can be used in both Worksheets and Userforms. It required VBA to function, you use Checkbox Properties to customize, set and read the values.

Sub sbAT_AddActiveXCheckboxesToSelectedRange()

    Dim rng As Range
    Dim cell As Range
    Dim chkBox As OLEObject

    ' Define the range where you want to insert checkboxes
    Set rng = Selection 'ThisWorkbook.Sheets("Sheet1").Range("A1:A10") ' Modify sheet name and range as needed

    ' Loop through each cell in the range
    For Each cell In rng
        ' Create an ActiveX checkbox within the cell
        Set chkBox = cell.Worksheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
            Left:=cell.Left, Top:=cell.Top, Width:=cell.Width, Height:=cell.Height)

        ' Additional properties can be set if desired, like:
        chkBox.Object.Caption = ""
        chkBox.Object.BackColor = RGB(225, 225, 225) ' Set Color
    Next cell

End Sub

The above VBA code will add ActiveX CheckBoxes to a selected Range, it also remove the default text and sets the background color.

Add multiple CheckBoxes 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. 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. This code will prompt the user to select the required range to add the Tick Boxes.

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

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