Contextures

Excel VBA - Worksheet Check Boxes

Use 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. The check box is positioned at the top left corner of the cell, and the caption is set as "Active".

The check box is linked to the cell in which it is positioned. To link to a different cell, 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.

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

The OnAction line that sets the check box to run a macro has been commented out. To enable it, remove the apostrophes, and change "mycbxMacro" to a valid macro name in the workbook.

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:E6")
'Set rngCB = Selection
strCap = "Active"

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

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

Sub MarkCheckBoxes()
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, and unchecked boxes 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, use 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.

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

Download the Sample File

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

Related Tutorials

Excel VBA Getting Started

FAQs, Excel VBA, Excel Macros

Create an Excel UserForm

UserForm with ComboBoxes

Edit Your Recorded Macro

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

 

 

 

 

 

Excel UserForms for Data Entry

 

 

 

 



Last updated: March 28, 2016 7:06 PM