How to create excel checklist box with VBA?

Apply the below VBA codes against a specific excel spreadsheet to get the checklist box to automatically appear before each text/number typed into a cell.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim cell As Range
If Target.Row = 2 Then Exit Sub
If Target.Cells.Count > 1 Then
    For Each cell In Target
        Call AddRemoveChkbx(cell)
    Next cell
    Set cell = Target
    Call AddRemoveChkbx(cell)
End If
Application.ScreenUpdating = True
End Sub

Sub AddRemoveChkbx(cell As Range)
Dim CLeft As Double, CTop As Double, CHeight As Double, CWidth As Double
Dim chkbx As CheckBox
If cell.Value <> "" Then
    With cell.Offset(0, -1)
        CLeft = Cells(.Row, .Column).Left + Cells(.Row, .Column).Width
        CTop = Cells(.Row, .Column).Top
        CHeight = Cells(.Row, .Column).Height
        CWidth = Cells(.Row, .Column).Width
    End With
    ActiveSheet.CheckBoxes.Add(CLeft, CTop, CWidth, CHeight).Select
    With Selection
        .Caption = ""
        .Value = xlOff
    End With
   cell.Offset(1, 0).Select
    For Each chkbx In ActiveSheet.CheckBoxes
        If cell.Top = chkbx.Top Then
            cell.Font.Strikethrough = False
        End If
    cell.Offset(1, 0).Select
End If
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *