Contextures

Worksheet Check Box - To Do List

Add check boxes in an Excel worksheet, for items in a To Do list. Next, connect them to a worksheet cell, to keep track of the completed items.

NOTE: For a quicker way to add check boxes, use the macro from the Excel VBA - Check Boxes page

-- To Do List

-- Add a Check Box

-- Copy the Check Box

-- Link Check Boxes to Cells

-- Macro to Link Check Boxes

-- Run Check Box Link Macro

-- Test the Check Boxes

-- Get the Sample File

To Do List

In this example, there is To Do list on an Excel worksheet. Each row in the To Do list contains one task.

This tutorial shows how to add a check box in each row, to mark the task as completed.

To Do list on an Excel worksheet

Add a Check Box

To insert a check box, follow these steps:

  • Click the Developer tab on the Ribbon.
  • In the Controls group, click the Insert command
  • There are two types of check boxes - Form Control and ActiveX Control.

insert form controls check box

  • For this tutorial, click the check box from the Form Controls section
    • NOTE: This type of control can be used on both Windows and Mac Excel.
  • On the worksheet, click near the top left corner of the cell where you want to add the check box -- cell B4 in this example.

click to add check box

Adjust the Check Box

  • The top left border of the check box frame should be inside cell B4.
  • If necessary, move the check box down or right, so the top left corner of the frame is inside the cell.

move the check box

Remove Check Box Text

The check box has default text when it is created, so follow these steps to remove it.

  • Click inside the check box frame, then select all the text, and delete it.
  • Make the check box frame narrower, so it just fits the box.

Remove Check Box Text

Copy the Check Box

Now that you've created and formatted one check box, you can copy it, and paste copies into the other cells.

Follow these steps to create the additional check boxes

  • Press Ctrl, and click on the check box, if it is not already selected.
  • On the Ribbon's Home tab, click Copy (or use the Ctrl + C shortcut)
  • Select cell B5, and paste the check box
  • Then the select and paste steps for cells B6 and B7

create additional check boxes

Link Check Boxes to Cells

The next step is to link each check box to a worksheet cell, so you can capture the check box result

If there are only a few check boxes, you can link them manually, by following the steps below. If there are many check boxes to link, it's quicker to use a macro, like the one in the next section.

Follow these steps to link a check box to a worksheet cell:

  • To select the check box in cell B4, press the Ctrl key, and click on the check box
  • Click in the Formula Bar, and type an equal sign =
  • Click on the cell that you want to link to, and press Enter
  • You can see the link formula in the screen shot below:  =$D$4
  • Repeat these steps to link each check box to the "Done" cell in its row, or use the macro in the next section

NOTE: You can also link to a cell by going into the check box's properties, but that takes longer.

Link  Check Boxes to Cells

Instead of manually linking the remaining check boxes, we’ll use the following macro, named LinkCheckBoxes.

To add this code to your own workbook, copy it to a regular code module.

Sub LinkCheckBoxes()
Dim chk As CheckBox
Dim lCol As Long
lCol = 2 'number of columns to right for link
For Each chk In ActiveSheet.CheckBoxes 
  With chk
    .LinkedCell = _  
    .TopLeftCell.Offset(0, lCol).Address
  End With
Next chk
End Sub

Run Check Box Link Macro

Next, follow these steps to run the check box linking macro:

  • Go to the sheet with the check boxes
  • Click the Ribbon’s View tab, and at the far right, click Macros
    • If a drop down list appears, click View Macros
  • In the list, click LinkCheckBoxes
  • Click Run.

NOTE: You won’t see anything happen on the worksheet, but each check box will be linked to the cell two columns to its right.

Test the Check Boxes

To test the check boxes, click on each one.

  • If you add a check mark, the cell two columns to the right should show TRUE.
  • If you clear a check box, the linked cell should show FALSE.
  • If you clear the linked cell, the check box will also be cleared.

Test the Check Boxes

Get the Sample File

To see the completed workbook, get the Excel Check Box To Do List sample file.

NOTE: The workbook also has macros that add the date in column E, when you add a check mark. And if you clear the check mark, the date is also cleared.

More Tutorials

Excel Formula CheckBox

Order Form with Billing Check Box

Check Box Macros

Get weekly Excel tips from Debra

 

 

Last updated: July 12, 2021 2:58 PM