Home > Macros > Basics > Check Box
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.
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.
- 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.
Adjust the Check Box
- The top left border of the check box frame should be inside cell B4.
necessary, move the check box down or right, so the top left corner of the
frame is inside the cell.
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.
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
- Select cell B5, and paste the check box
- Then the select and paste steps for cells B6 and B7
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
- 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.
Macro to Link Check Boxes
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.
Dim chk As CheckBox
Dim lCol As Long
lCol = 2 'number of columns to right for link
For Each chk In ActiveSheet.CheckBoxes
.LinkedCell = _
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.
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.
Excel Formula CheckBox
Delete or Add Worksheet Objects
Order Form with Billing Check Box
Check Box Macros