Sub Insert_Form_Checkboxes()
Dim myCell As Range, myRng As Range
Dim CBX As CheckBox
With ActiveSheet
'delete ALL existing checkboxes from sheet, links are NOT cleared.
.CheckBoxes.Delete '<~~~~~ comment out to NOT delete existing checkboxes
'Exit Sub '<~~~~~ uncomment when deleting checkboxes only.
Set myRng = .Range("o3:as15") '<~~~~~ enter the range to have checkboxes
End With
Application.ScreenUpdating = False
For Each myCell In myRng.Cells
With myCell
Set CBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Left:=.Left, _
Width:=.Width, Height:=.Height) 'click area same size as cell
CBX.Name = "CBX_" & .Address(0, 0) 'name includes address of checkbox
CBX.Caption = "" 'whatever you want, "" for none
CBX.Value = xlOff 'initial value unchecked
CBX.LinkedCell = .Offset(0, 0).Address 'linked to the cell its in
CBX.OnAction = "RunForAllChkBoxes" 'run this each time clicked
'format the cell
.NumberFormat = ";;;" 'make cell contents not show
.Interior.ColorIndex = 56 'color black to start
End With
Next myCell
Application.ScreenUpdating = True
End Sub
Sub RunForAllChkBoxes()
Dim fromWhere As String
With ActiveSheet.CheckBoxes(Application.Caller)
fromWhere = Split(Application.Caller, "_")(1)
If .Value = xlOff Then
Range(fromWhere).Interior.ColorIndex = 56
Else
Range(fromWhere).Interior.ColorIndex = 4
End If
End With
End Sub