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