Home > Macros > Basics > Check Boxes Worksheet Check Box Macros ✅ Use Excel VBA macros to add check boxes to a worksheet, link check boxes to cells, check or clear all check boxes, and assign macros to check boxes. Thanks to Dave Peterson for his contributions to this page.

Add Check Boxes

The following code will add a Forms check box to each cell in the specified range. When added, the check box:

• Is positioned at the top left corner of the cell
• Its caption is set as "Test"
• Its width is the same as the cell's width

In the code sample below, the specified range is B2:E6 on the active sheet. You can edit the code to put the check boxes into a different range

     Set rngCB = wks.Range("B2:B11")

Add Check Boxes to Selected Cells

In the sample file, there is a workbook button - Add to Selection. That adds check boxes to the currently selected cells.

In the codc, instead of using a specific range of cells, the rngCB variable is set to the selection.

     Set rngCB = Selection

Linked Cell

The check box is linked to the cell in which it is positioned. To link to a different cell, you can edit the code:

For the LinkedCell property, change the numbers in the Offset property.

• The first number is Rows -- use a positive number to move down, and negative to move to up.
• The second number is columns -- use a positive number to move to the right, and negative to move to the left.

For example, use this setting to link to the cell that is 2 columns to the left (-2)

 .LinkedCell = c.Offset(0, -2) _ 

Run Macro On Click

In the code below, the OnAction line has been commented out, by typing an apostrophe at the start of each line. That prevents those lines of code from running.

'    .OnAction = ThisWorkbook.Name _
'        & "!mycbxMacro"

If you want to add check boxes that run a specific macro, you can change the code:

• To enable the lines of code, remove the apostrophes
• Then, change mycbxMacro to a valid macro name in your workbook

Code to Add Check Boxes to Specific Range

Here is the code that you can copy to a regular module in your workbook. This code is also in the sample file below, and you can test it there, before using it in your own workbooks.

Sub AddCheckBoxesRange()
'by Dave Peterson
'add Form checkboxes
Dim c As Range
Dim myCBX As CheckBox
Dim wks As Worksheet
Dim rngCB As Range
Dim strCap As String

Set wks = ActiveSheet
Set rngCB = wks.Range("B2:B11")
'Set rngCB = Selection
strCap = "Test"

For Each c In rngCB
  With c
    Set myCBX = wks.CheckBoxes.Add _
      (Top:=.Top, Width:=.Width, _
       Height:=.Height, Left:=.Left)
  End With
  With myCBX
    .Name = "cbx_" & c.Address(0, 0)
    .LinkedCell = c.Offset(0, 0) _
        .Address(external:=True)
    .Caption = strCap
'    .OnAction = ThisWorkbook.Name _
'        & "!mycbxMacro"
  End With
Next c

End Sub

Delete All Check Boxes

The following code will remove all the Forms check boxes from the active worksheet. In the sample file, there is a worksheet button to run this macro

Sub DeleteAllCheckboxes()
  ActiveSheet.CheckBoxes.Delete
End Sub

Check All Check Boxes

The following code will add a check mark to all the Forms check boxes on the active worksheet.

NOTE: There are two more macros below:

• Clear All Check Boxes
• Toggle All Checkboxes
• In the sample file, there are worksheet buttons to run these macros

Sub MarkCheckBoxes()
'check all boxes
Dim chk As CheckBox
Dim ws As Worksheet
Set Ws = ActiveSheet
For Each chk In ws.CheckBoxes
  chk.Value = True
Next chk
End Sub

Clear All Check Boxes

The following code will remove the check mark from all the Forms check boxes on the active worksheet

Sub ClearCheckBoxes()
Dim chk As CheckBox
Dim Ws As Worksheet
Set Ws = ActiveSheet
For Each chk In ws.CheckBoxes
  chk.Value = False
Next chk
End Sub

Toggle Check Boxes

The following code will change the setting for each Forms check box on the active worksheet:

• If the box is checked, it will be unchecked
• If the box is unchecked, it will be checked

Sub ToggleCheckBoxes()
'xlOn= 1 and xlOff= -4146
Dim chk As CheckBox
For Each chk In ActiveSheet.CheckBoxes
  chk.Value = xlOn + xlOff - chk.Value
Next chk
End Sub

Link Check Boxes to Cell

The following code will link each Forms check box on the active worksheet to the cell on which its top left corner is positioned

Sub LinkCheckBoxes()
Dim chk As CheckBox
Dim Ws As Worksheet
Set Ws = ActiveSheet
For Each chk In ws.CheckBoxes
  With chk
    .LinkedCell = _
      .TopLeftCell.Address
  End With
Next chk

End Sub

To link to a different cell, you can edit the code:

For the LinkedCell property, change the numbers in the Offset property.

• The first number is Rows -- use a positive number to move down, and negative to move to up.
• The second number is columns -- use a positive number to move to the right, and negative to move to the left.

For example, in the following macro, check box is linked to the cell that is 2 columns to its left (-2)

Sub LinkCheckBoxesOffset()
Dim chk As CheckBox
Dim Ws As Worksheet
Set Ws = ActiveSheet
For Each chk In ws.CheckBoxes
  With chk
    .LinkedCell = _
      .TopLeftCell.Offset(0, -2).Address
  End With
Next chk

End Sub

Assign Macro to Check Boxes

The following code will assign a macro to each of the Forms check boxes on the active worksheet

Sub SetCheckBoxesMacro()
Dim chk As CheckBox
Dim Ws As Worksheet
Set Ws = ActiveSheet
For Each chk In ws.CheckBoxes
   chk.OnAction = "CheckBoxDate"
Next chk
End Sub

Get the Sample File

To use the check box code, you can get the sample file. The zipped file is in xlsm format, and contains macros.

Related Tutorials

Excel VBA Getting Started

To Do List with Check Boxes

Order Form with Billing Check Box

FAQs, Excel VBA, Excel Macros

Create an Excel UserForm

UserForm with ComboBoxes

Edit Your Recorded Macro