Excel- Tick Marks and Check Boxes in cells

Check boxes, a pipe-dream?

It isn't much to ask for is it? Cells with tick marks (or check boxes if you like) would tick all the boxes for me. After all, you can easily create Yes/No fields in Microsoft Access tables and creating check boxes in Apple Numbers is a breeze but poor old Excel just doesn't seem to want to play the check box game.

Yes, you can draw Form Controls or ActiveX Controls on a worksheet and link them to the cells but it is such a long-winded and painful process when you need loads of check boxes. I want an entire column full of check boxes, I want them in the cell and not on the cell and I want to do them quickly and easily. Dream on.

Rant over, the awful truth is that if the Excel designers don't give you exactly what you want then you have to do it yourself and here's how to do a home-brew version of an Excel in-the-cell checkbox. It involves doing an Excel macro and creating a shortcut for it but don't let that put you off as it only takes a few minutes and then you can have as many check boxes or tick marks as you like whenever you like.

Step One. Record a Macro

You need a module to store your macro and the easiest way to get one is to turn on the macro recorder and then turn it off again. Click Record Macro in the Macros control on the View tab.

Recording a macro

When this dialog displays, enter the Macro name. This one is named as "TickToggle" but you can name it as you like (no spaces are allowed in macro names) Set the shortcut key by typing a letter into the Shortcut key box. I am using the letter "t" (t for ticks!) Try not to use a shortcut key that you want to keep for other purposes, like maybe CTRL+C.

The final setting is to drop the list for Store macro in and select Personal Macro Workbook. Click the OK button, click a cell on your worksheet and then click Stop Recording in the Macros control on the View tab.

Step Two. Copy and Paste the Tick Mark code

Now the macro has been recorded you need to find it and substitute the recorded macro with the macro code that does the tick marks.

Unhiding PERSONAL

The Personal Macro Workbook is a hidden document so we need to unhide it first. Click the Unhide control in the Window group on the View tab and choose PERSONAL from the list.

Then we need to see the code of the recorded macro. Click View Macros in the Macros control on the View tab, select "TickToggle" in the listing and then click the Edit button on the right hand side.

This will transfer you to the macro editor and your screen should be looking something like the illustration below.

The recorded macro

Don't worry if it's not exactly the same so long as you have the first and last lines the same. Everything between the Sub and End Sub lines will be replaced.

The final part of this step is to copy and paste our new tick mark macro, save the file and exit from the editor.




Here's the macro code to copy and paste:

Sub TickToggle()

    On Error Resume Next

    If ActiveCell = "" Then
        With ActiveCell
            .Formula = "=CHAR(252)"
            .Font.Name = "Wingdings"
        End With
    Else
        ActiveCell.Clear
    End If

End Sub

The finished macro

Here's the macro in the editor after the above code has been pasted. Translating the code into plain English, it reads as follows "If the cell is empty then enter character 252 into the cell and set the font format as Wingdings, otherwise clear the cell". Character 252 in Wingdings is a tick mark.

Choose File, Save in the menu and then exit from the editor and return to Excel by choosing File, Close. When you're back in Excel click the Hide control in the Window group on the View tab to hide PERSONAL.

Step Three. Test the macro

Click a cell, press your shortcut keys CTRL+t and a tick mark appears in the cell, press them again and the tick mark disappears.

Tick marks!

Centre-align the cell if you want your ticks centred and change the column width setting.

Your macro is stored in your Personal Macro Workbook which opens automatically when you start Excel and you can use this macro on any workbook. See the next section if you prefer check boxes to tick marks.

Ticks or Boxes?

There's a big decision to make here; do you want to have a tick in the cell and then an empty cell when you turn it off or do you want a proper check box with a tick in it which then changes to an empty box when you turn it off?

The macro above is the "plain tick or an empty cell" version, if you want the "ticked box or un-ticked box" version then you need to change the macro code so that the correct character is entered into the cell. The plain tick is Wingdings character 252, the ticked check box is character 254 and the un-ticked box is character 168. Change your macro by copying and pasting the code below. 

The logic required is slightly different, now it reads in plain English as "If the cell is empty or if it contains character 168 (an un-ticked box) then enter character 254 (a ticked box), otherwise enter character 168".

Copy and Paste this macro for check boxes

Sub TickToggle()

    On Error Resume Next

    If ActiveCell = "" Or ActiveCell.Formula = "=CHAR(168)" Then
        With ActiveCell
            .Formula = "=CHAR(254)"
            .Font.Name = "Wingdings"
        End With
    Else
        With ActiveCell
            .Formula = "=CHAR(168)"
            .Font.Name = "Wingdings"
        End With
    End If
 
End Sub

Checking if the cell has been ticked

If all you need is a simple tick or checkbox in the cell then you're fine but what if you have to have a formula or some Conditional Formatting in the worksheet do something as a result of the cell being ticked or the box checked?

Usually you would use a formula to make a logical test giving you TRUE or FALSE so that you could do something to respond to the current state of the check box. Here's the sort of logical constructions that can make the relevant tests:

Check Tests

Checking for the tick-mark or empty cell is the easiest one as all you are doing is asking "is the cell empty?", which can be done like this:

=ISBLANK(A1)

Where A1 is the cell containing the tick. ISBLANK returns FALSE if the cell contains a tick. Should you find the test easier to process if you return TRUE for a ticked cell then reverse the logic as follows:

=NOT(ISBLANK(A1))

The check box is a little harder as you asking "does the cell contain character 254?" (a ticked box) and you have to use Excel's CELL function as follows:

=CELL("contents",A1)=CHAR(254)

Happy ticking...

Related Posts

Excel-Calculating age from date of birth

Excel-Calculations without formulas

Excel-Sorting by last name

Excel-Switching columns to rows

Excel-CTRL key tricks

Excel-Double click tricks

Excel-Converting USA dates to UK dates

Excel Top ten shortcut keys

Excel 2010 shortcut keys

Excel-AutoSum Revisited

Excel for Mac-Deleting a range and other shortcuts

Excel-Make Pivot Tables Refresh automatically

Training Courses

If you've still got that "I just don't know what I'm doing" feeling then you might like to arrange an Excel training course for yourself or with some of your colleagues. It's really easy to book one of our courses and they're great value for money. See our website for full details.