Add Forms Checkboxes Dynamically To Excel Rows
The code below shows how to create a forms checkbox in one, several or all rows of a worksheet.
Checkboxes come in two flavors: Forms checkboxes (which do not produce any code in the back end but can be linked to a worksheet cell) and Active-X checkboxes, which trigger events coded in VBA. The checkboxes in this example are FORMS checkboxes, not Active-X checkboxes.
Suppose in a given worksheet there are 100 rows, each row with a checkbox in the same column. For this example, let's say the user clicks 23 of the 100 checkboxes to identify the rows that they want to process with a macro. The code below shows how scan through all the checkboxes and determine which rows are selected (each checkbox is associated with a cell (hence a row) when it is created).
There is a VERY LARGE CAVEAT to consider. If checkboxes are generated and then the user deletes rows that contain the checkboxes, the actual checkbox is NOT deleted, and will create an issue with programming that doesn't consider that situation. At the very bottom of this code example is a series of three procedures. One will add checkboxes. The second will delete selected rows. And finally, the third will clean up and delete "stranded" checkboxes that have lost their reference to the linked cell in the row that was deleted. You can use checkboxes in Excel rows, but keep in mind that if the row is deleted, you must perform the cleanup operation.
Program Code
Option Explicit
Sub SampleOfCreateCheckbox()
' ************************************************************************************
' Manipulating Forms Checkboxes
' ************************************************************************************
Dim chk As CheckBox
Dim lngRow As Long
Dim j As Long
Dim shtActive As Worksheet
' ************************************************************************************
' Set Worksheet Variable
' ************************************************************************************
Set shtActive = ActiveSheet
' ************************************************************************************
' Create 5 checkboxes in rows 20 through 24
' ************************************************************************************
For j = 20 To 24
With shtActive.CheckBoxes
' ************************************************************************************
' Create a Forms (Not Active-X) Checkbox Associated with a cell location
' ************************************************************************************
.Add(Range(Cells(j, 1).Address).Left, Range(Cells(j, 1).Address).Top, Width:=15, Height:=15).Select
' ************************************************************************************
' Note - The Range Form Used Below Has The Same Results as the statement above:
' .Add(Range(Cells(j, 1), Cells(j, 1)).Left, Range(Cells(j, 1), Cells(j, 1)).Top, Width:=15, Height:=15).Select
' ************************************************************************************
Selection.Value = xlOn ' Turn On The CheckMark
Selection.Characters.Text = "" ' Remove the Caption
' '(Note: .Caption = "" Does Not Work Correctly With Forms Checkboxes)
End With
Next j
' ************************************************************************************
' Scan All CheckMarks That Are Checked And Determine The Associated Row
' ************************************************************************************
For Each chk In shtActive.CheckBoxes
If chk.Value = xlOn Then ' See If The CheckBox Is On
Debug.Print chk.Name ' Print the Name of the CheckBox
Debug.Print chk.Value ' Print The Numeric Value of the CheckBox
Debug.Print chk.Caption ' Print the Caption
Debug.Print chk.TopLeftCell.Address ' Print The Cell Address Associated With The Checkbox
lngRow = Range(chk.TopLeftCell.Address).Row ' Compute the Row Associated With The Checkbox
Debug.Print lngRow ' Print The Row Associated With The CheckBox
End If
Next chk
' ************************************************************************************
' Scan All CheckMarks That Are Not Checked And Determine The Associated Row
' ************************************************************************************
For Each chk In shtActive.CheckBoxes
If chk.Value = xlOff Then ' See If The CheckBox Is Off
Debug.Print chk.Name ' Print the Name of the CheckBox
Debug.Print chk.Value ' Print The Numeric Value of the CheckBox
Debug.Print chk.Caption ' Print the Caption
Debug.Print chk.TopLeftCell.Address ' Print The Cell Address Associated With The Checkbox
lngRow = Range(chk.TopLeftCell.Address).Row ' Compute the Row Associated With The Checkbox
Debug.Print lngRow ' Print The Row Associated With The CheckBox
End If
Next chk
' ************************************************************************************
' Turn Off All The Checkboxes
' ************************************************************************************
For Each chk In shtActive.CheckBoxes
chk.Value = xlOff
Next chk
Cells(1, 1).Select
End Sub
' **********************************************
' Next Example Is A Simple Alpha Cell Reference
' ********************************************
Option Explicit
Sub SampleOfCreateCheckbox()
Dim shtActive As Worksheet
Set shtActive = ActiveSheet
Dim chk As CheckBox
Dim lngRow As Long
With shtActive.CheckBoxes
.Add(Range("A20").Left, Range("A20").Top, Width:=15, Height:=15).Select
Selection.Value = xlOn
Selection.Characters.Text = "Rich"
.Add(Range("A22").Left, Range("A22").Top, Width:=15, Height:=15).Select
Selection.Value = xlOn
Selection.Characters.Text = "Joe"
.Add(Range("A24").Left, Range("A24").Top, Width:=15, Height:=15).Select
Selection.Value = xlOn
Selection.Characters.Text = "Mark"
End With
For Each chk In shtActive.CheckBoxes
If chk.Value = xlOn Then
Debug.Print chk.Name
Debug.Print chk.Value
Debug.Print chk.Caption
Debug.Print chk.TopLeftCell.Address
lngRow = Range(chk.TopLeftCell.Address).Row
Debug.Print lngRow
End If
Next chk
For Each chk In shtActive.CheckBoxes
chk.Value = xlOff
Next chk
End Sub
Program Code To Clean Up Checkboxes From Deleted Rows
Option Explicit
Sub AddCheckBoxes()
' *************************************************************
' Add Checkboxes For Demonstration Purposes
' *************************************************************
Dim b As Boolean
Dim i As Long
Dim d As Double
Dim s As String
Dim chk As CheckBox
Dim cel As Range
ActiveSheet.CheckBoxes.Delete
ActiveSheet.UsedRange.ClearContents
For i = 1 To 25
d = d + 12
With Cells(i, 1)
b = Not b
s = .Address
.Value = False
.Font.Color = vbWhite
.Offset(0, 8).Formula = "=" & s
.Offset(0, 9).Value = "orig row " & i
With ActiveSheet.CheckBoxes.Add(.Left + d, .Top, 15, 15)
.Value = IIf(b, xlOn, xlOff)
.Caption = ""
.LinkedCell = s
End With
End With
Next
On Error Resume Next
End Sub
Sub DelSomeRows()
' *************************************************************
' Delete Some Rows To Created Checkboxes With No Valid
' Linked Cell
' *************************************************************
Dim i As Long
For i = 1 To 25 Step 3
Rows(i).Delete
Next
End Sub
Sub DelHidden()
' *************************************************************
' Delete Stranded Check Boxes That Lost Their Cell Reference
' And Position The Checkboxes Back To Their Linked Cell
' *************************************************************
Dim chk As CheckBox
For Each chk In ActiveSheet.CheckBoxes
If chk.LinkedCell = "#REF!" Then
chk.Delete
Else
' Demo How To Ensure Checkboxes Are Positioned Correctly
With Range(chk.LinkedCell)
If chk.Left <> .Left Then chk.Left = .Left
If chk.Top <> .Top Then chk.Top = .Top
End With
End If
Next
End Sub
Program Code To Emulate Checkboxes And Avoid Deleted Row Issue
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' *********************************************************************
' Simulate Checkboxes Using Wingdings 2 and Values of 0 and 1
' *********************************************************************
' R E S T R I C T I O N S:
' (1) This Method Can Not Be Used on Merged Cells
' (2) If The Column Width is less than 18 pixels, then the cell should
' be preformatted to a number format of "\R;;£" to prevent the
' cell from expanding the column width - otherwise no preformatting
' of the cell is required - Setting the Number Format with VBA
' as shown below: .NumberFormat = "\R;;£" causes the column width
' to expand to 18 pixels.
' *********************************************************************
'
' *********************************************************************
' Toggle The CheckBox To Simulate Clicking A Regular Checkbox
' *********************************************************************
' The Contents Toggles Between 0 and 1
' Note: The .Number Format Shows Values For Positive, Negative, Zero
' Hence, "\R;;£" Has Three Sections:
' (1) If the number is positive, an "R" is displayed
' (2) The number won't be negative so no effect
' (3) If the number is zero, an "£" is displayed
' In Wingdings 2, an "R" is a checked checkbox
' In Wingdings 2, an "£" is an unchecked checkbox
' *********************************************************************
' *********************************************************************
' Exit If Double Click Is Not In Desired Column ("A" for this Example)
' *********************************************************************
'If Target.Column <> 1 Then Exit Sub
With Target
.Font.Size = 6
.Font.Name = "Wingdings 2"
.Value = 1 - .Value
If .NumberFormat <> "\R;;£" Then
.NumberFormat = "\R;;£"
.HorizontalAlignment = xlCenter
End If
End With
' *********************************************************************
' Exit out of edit mode
' *********************************************************************
Cancel = True
End Sub