To-Do list3

Today I will share a To-do list excel template with you. You can add text to the sheet and excel creates checkboxes instantly. Click a checkbox and the related text is marked as finished. Also, if you delete text the corresponding checkbox is deleted automatically.

Download excel *.xlsm file

To-Do List Template.xlsm

VBA code

Event code

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
Else
    Set cell = Target
    Call AddRemoveChkbx(cell)
End If
Application.ScreenUpdating = True
End Sub

Macros

Sub ClickChkbx()
For r = 1 To Rows.Count
    With ActiveSheet.Shapes.Range(Array(Application.Caller))
        If Cells(r, 1).Top = .Top Then
            For c = 1 To Columns.Count
                If Cells(r, c).Left = .Left + 24 Then
                    With Cells(r, c).Font
                        .Strikethrough = Not .Strikethrough
                    End With
                    Exit For
                End If
            Next c
            Exit For
        End If
    End With
Next r
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 / 2
        CTop = Cells(.Row, .Column).Top
        CHeight = Cells(.Row, .Column).Height
        CWidth = Cells(.Row, .Column).Width / 2
    End With
    ActiveSheet.CheckBoxes.Add(CLeft, CTop, CWidth, CHeight).Select
    With Selection
        .OnAction = "ClickChkbx"
        .Caption = ""
        .Value = xlOff
        .Display3DShading = False
    End With
    cell.Offset(1, 0).Select
Else
    For Each chkbx In ActiveSheet.CheckBoxes
        If cell.Top = chkbx.Top Then
            chkbx.Delete
            cell.Font.Strikethrough = False
        End If
    Next
    cell.Offset(1, 0).Select
End If
End Sub

Bonus tip - Quickly insert checkboxes

You can quickly insert many checkboxes without inserting them one by one.

  1. Go to Developer tab on the ribbon
  2. Click "Insert" button on the ribbon
  3. Place a checkbox on a cell
  4. Select the cell
  5. Click and hold on black dot on lower right corner
  6. Drag down
  7. Release button

Copy checkboxes1

Don´t try this tip with the attached file. The vba code inserts a checkbox automatically as soon as you type in a cell.