Today I will share a To-do list excel template with you. You can add text to the sheet and an Excel macro creates corresponding checkboxes instantly. Press with left mouse button on a checkbox and the related text is marked as finished. Also, the corresponding checkbox is automatically removed if you delete text.

The animated image below demonstrates how to create new items and mark items finished.

How to add checkboxes?

Type in any cell and a checkbox is instantly and automatically created on the adjacent cell to the left. Checkboxes are not created if multiple cells are simultaneously entered at the same time.

An event macro is what it takes to make this possible. They are stored in a worksheet module contrary to a regular macro that is stored ina regular module.

Back to top

How to remove checkboxes?

Simply select a cell containing text and press the "Delete" key. The corresponding checkbox is deleted by a macro.

You can also manually delete checkboxes, here is how. Press and hold CTRL-key. Press with left mouse button on with the left mouse button on the checkbox you want to delete. Press the "Delete" key.

The macro below that lets you delete all checkboxes on a worksheet.

Sub RemoveCheckboxes()
ActiveSheet.CheckBoxes.Delete
End Sub

Back to top

How to run a macro?

Run macro RemoveCheckboxes if you want to delete all checkboxes on a worksheet.

1. Press shortcut keys Alt + F8 to open the Macro dialog box.
2. Press with mouse on the macro name to select it.
3. Press with left mouse button on the "Run" button.

The list in the "Macro" dialog box shows all available macros, if RemoveCheckboxes is not there you need to first save it to a module. Instructions below.

Back to top

Event code

'Event code that runs when a cell has a new value
Private Sub Worksheet_Change(ByVal Target As Range)

'Disable screen refresh
Application.ScreenUpdating = False

'Dimension variable and declare data types
Dim cell As Range

'Check if changed cell's row is 2
If Target.Row = 2 Then Exit Sub

'Check if more than one cell has been changed
If Target.Cells.Count > 1 Then

    'Iterate through each changed cell
    For Each cell In Target
  
        'Run macro named AddRemoveChkbx with parameter cell
        Call AddRemoveChkbx(cell)

    'Continue with next cell
    Next cell

'Go from here if only one cell has been changed
Else

    'Save target cell to object named cell
    Set cell = Target

    'Run macro AddRemoveChkbx with parameter cell
    Call AddRemoveChkbx(cell)
End If

'Enable screen refresh
Application.ScreenUpdating = True
End Sub

Back to top

Where to put the Event code?

Event code is stored in the worksheet or workbook module, in this case, the worksheet module.

1. To access the worksheet module press with right mouse button on on a worksheet tab.
2. A pop-up menu appears, press with left mouse button on "View Code". See the image above.
3. The Visual Basic Editor opens and the worksheet module is displayed.
   
4. Paste the code to the worksheet module.

Back to top

VBA Macros

'Name macro
Sub Press with left mouse button onChkbx()

'Iterate from 1 to the number of rows in your worksheet
For r = 1 To Rows.Count

    'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
    With ActiveSheet.Shapes.Range(Array(Application.Caller))

        'Check if cell's top value is equal to the press with left mouse button oned check box, based on variable r
        If Cells(r, 1).Top = .Top Then

            'Iterate from 1 to the number of columns in your worksheet
            For c = 1 To Columns.Count

                'Check if cell's left value is equal to checkbox's left value + 24
                If Cells(r, c).Left = .Left + 24 Then

                    'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
                    With Cells(r, c).Font

                        'Apply strikethrough to text based on variables r and c
                        .Strikethrough = Not .Strikethrough
                    End With

                    'Stop For ... Next statement
                    Exit For
                End If
            Next c

            'Stop For ... Next statement
            Exit For
        End If
    End With
Next r

End Sub

'Name macro, dimension parameteres and declare data types
Sub AddRemoveChkbx(cell As Range)

'Dimension variables and declare data types
Dim CLeft As Double, CTop As Double, CHeight As Double, CWidth As Double
Dim chkbx As CheckBox

'Check if cell value is not equal to nothing
If cell.Value <> "" Then

    'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
    With cell.Offset(0, -1)
        
        'Add half of width of a cell to the left property value and save to variable CLeft
        CLeft = Cells(.Row, .Column).Left + Cells(.Row, .Column).Width / 2

        'Save top property value to variable CTop
        CTop = Cells(.Row, .Column).Top

        'Save Height property value to variable CHeight 
        CHeight = Cells(.Row, .Column).Height

        'Save width property value divided by 2 to variable CWidth
        CWidth = Cells(.Row, .Column).Width / 2
    End With

    'Create checkbox based on variables CLeft, CTop, CWidth, CHeight
    ActiveSheet.CheckBoxes.Add(CLeft, CTop, CWidth, CHeight).Select

    'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
    With Selection

        'Assign macro Press with left mouse button onChkbx to checkbox
        .OnAction = "Press with left mouse button onChkbx"

        'No caption
        .Caption = ""

        'Unchecked checkbox
        .Value = xlOff

        'No 3D shading
        .Display3DShading = False
    End With

    'Select cell below
    cell.Offset(1, 0).Select

'If cell has value continue here
Else

    'Iterate through all checkboxes on active worksheet
    For Each chkbx In ActiveSheet.CheckBoxes

        'Check If cell's top value matches checkboc top value
        If cell.Top = chkbx.Top Then

            'Remove checkbox from worksheet
            chkbx.Delete

            'Remove strikethrough from cell
            cell.Font.Strikethrough = False
        End If
    Next

    'Select cell below
    cell.Offset(1, 0).Select
End If
End Sub

Back to top

Where to put the macros?

1. Press shortcut keys Alt+ F11 to open the Visual Basic Editor.
2. Press with left mouse button on "Insert" on the top menu, see image above.
3. Press with left mouse button on "Module" to create a new module in your workbook.
4. Copy above VBA code.
5. Paste VBA code to code window, see blue arrow in the image above.

Back to top

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. Press with left mouse button on "Insert" button on the ribbon
3. Place a checkbox on a cell
4. Select the cell
5. Press and hold on black dot on lower right corner
6. Drag down
7. Release button

You don't need to do this with the attached file, the VBA code inserts a checkbox automatically as soon as you type in a cell.

Back to top

Get the Excel file

To-Do-List-Template.xlsm

Back to top

Recommende reading

The Best To Do List Templates in Excel

Simple to-do list

Excel To Do List Template – 4 Examples

Check boxes category

Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]

Copy selected rows (checkboxes) (2/2)
This article demonstrates a macro that copies selected rows based on enabled check boxes. The image above shows data on […]

Add checkboxes to a sheet (1/2) [VBA]
In this post, I will demonstrate a macro that creates checkboxes in nonempty rows, see checkboxes in column E in image […]

Templates category

Calendar – monthly view
This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]

Plot date ranges in a calendar part 2
I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]

Shift Schedule
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]

Excel categories

Comments (7)