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. |
The following code will add a Forms check box to each cell in the specified range. When added, the check box:
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")
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
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.
For example, use this setting to link to the cell that is 2 columns to the left (-2)
.LinkedCell = c.Offset(0, -2) _
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:
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
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
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:
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
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
The following code will change the setting for each Forms check box on the active worksheet:
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
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.
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
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
To use the check box code, you can get the sample file. The zipped file is in xlsm format, and contains macros.
Last updated: November 2, 2022 12:00 PM