Author: Oscar Cronquist Article last updated on September 22, 2020

Multi level todo list3

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. Click 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.

To-Do list3

How to add checkboxes?

Add check box

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.

How can I enter multiple cells simultaneously?

  • Select a cell range. Type the text. Press CTRL + Enter. All selected cells are now populated.
  • Copy a cell. Paste to a cell range. All cells in the cell range now contain the contents of the copied cell.

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?

remove check box

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. Click 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?

multi level todo lists run a macro 1

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. Click on the macro name to select it.
  3. Click 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?

Multi level todo list where to put event code

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

  1. To access the worksheet module right-click on a worksheet tab.
  2. A pop-up menu appears, click "View Code". See the image above.
  3. The Visual Basic Editor opens and the worksheet module is displayed.
    Multi level todo list where to put event code1
  4. Paste the code to the worksheet module.
Note, save the workbook with the file extension *.xlsm to attach code to workbook.

Back to top

VBA Macros

'Name macro
Sub ClickChkbx()

'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 clicked 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 ClickChkbx to checkbox
        .OnAction = "ClickChkbx"

        '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?

Multi level todo list where to put the code

  1. Press shortcut keys Alt+ F11 to open the Visual Basic Editor.
  2. Click "Insert" on the top menu, see image above.
  3. Click "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.
Note, save the workbook with the file extension *.xlsm to attach code to the workbook.

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. 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

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

Download 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