March 27, 2005

Checkboxes

I've used checkboxes (Forms toolbar) for a while now but always found them a little awkward. For example, if there are a lot of them on a Worksheet with a white background, they don't stand out very much and it's a bit hard to check the checkboxes (pun intended).

Here's a way to make them a bit more visible. (Be forewarned this will remove Conditional Formatting and use White font in the cells you select)

A little background first. All checkboxes can link to a cell (Optional). If you designate a linked cell (by right-clicking a Checkbox to bring up the Format Control dialog box), if ticked, the link cell returns TRUE, otherwise it will return FALSE. You can also change it's name using the Name Box.



Notice how I use the singular tense ("a" Checkbox, "it's" name). This is because they must be formatted one at a time. This really is annoying when you have several checkboxes you want to add or change.

This macro does it automatically by giving referencing each checkbox to each cell in a selection, both linked cell and the checkbox name. It also changes color to Yellow when ticked and back to White when unticked, using Conditional Formatting (just change the color index to get other colors if you prefer)

Sub AddCheckBoxes()
    On Error Resume Next
    Dim c As Range, myRange As Range
    Set myRange = Selection
    For Each c In myRange.Cells
        ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select
            With Selection
                .LinkedCell = c.Address
                .Characters.Text = ""
                .Name = c.Address
            End With
            c.Select
            With Selection
                .FormatConditions.Delete
                .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="=" & c.Address & "=TRUE"
                .FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked
                .FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked
                .Font.ColorIndex = 2 'cell background color = White
            End With
        Next
        myRange.Select
End Sub


This is how they will look.



Note: A non-VBA alternative is to use Marlett font, then type "a". This will do the same thing and you can use Conditional Formatting to change the color also.



Just so you know, checkboxes can be set not to print using the Properties tag of the Format Control dialog box. Use the Select Objects arrow on the Drawing toolbar to select them as you want.

Write a Comment
Can yo please help me how to create multiple checkboxes and asigning them relative referances. Now as I am about to create any additional checkbox it carries the reference of the cell of the parent checkbox. Unlike the manner it carries for Cells. Is there a shortcut way to create a realtive refernce.
Posted by Vishal at May 14, 2005 09:56
Hi Vishal,

I don't think you can do this.

Amended: Look at my new post Checkboxes Revisted

http://blog.livedoor.jp/andrewe/archives/50165806.html
Posted by Andrew at May 20, 2005 23:58
Is there a way to change the font of the text associated with the checkbox? I want to have a question listed next to each checkbox, but I need to have a much larger (and prettier) font.
Posted by Rhonda at November 03, 2005 00:50
Hi Rhonda,

Try this alternative. Do the font formatting in the column to the right and change the row height if necessary. Enter your questions, then with the original column on the left where the checkboxes will appear, change the columnn width to a smaller "squarish" size (not so small that the checkboxes will overlap the next column).

With the cells in the checkbox column selected (don't include the question column) run this code. (See next comment)
Posted by Andrew at November 03, 2005 16:54
Sub AddCheckBoxes()
On Error Resume Next
Dim c As Range, myRange As Range
Set myRange = Selection
For Each c In myRange.Cells
ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select
With Selection
.LinkedCell = c.Address
.Characters.Text = ""
.Name = c.Address
End With

Continued Below (Omit this line)
Posted by Andrew at November 03, 2005 16:56
Range(c, c.Offset(, 1)).Select
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & c.Address & "=TRUE"
.FormatConditions(1).Interior.ColorIndex = 6
End With
With c
.FormatConditions(1).Font.ColorIndex = 6
.Font.ColorIndex = 2
End With
Next
myRange.Select
End Sub

Hope it does what you want ;-)
Posted by Andrew at November 03, 2005 16:57
i want to know how do i remove checkbox from excel
Posted by prem at January 05, 2006 20:43
I too cannot get rid of the checkboxes from Excel. It can't be THAT difficult surely?!?
Posted by Davholsea at February 07, 2006 19:44
Sorry I didn't notice your comments until now. I get a few recently, some of them slip by :-)

There are 3 easy ways. Either right click them and remove them one by one, or push Select Objects (the button that looks like a cursor) on the Drawing Toolbar to remove several at once. (Push Esc when you are finished). If the Drawing Toolbar is not visible, right click any other toolbar and tick it where shown.

Continued below...
Posted by Andrew at February 07, 2006 22:43
Or run this code.

Sub RemoveCheckboxes()
On Error Resume Next
ActiveSheet.CheckBoxes.Delete
Selection.FormatConditions.Delete
End Sub

All checkboxes in the sheet will be deleted. All conditional formatting in selected cells will also be deleted. You will probably have to change your font color back to what it was before. (If you ran the AddCheckBoxes code I posted above, it should be white)
Posted by Andrew at February 07, 2006 22:44
I have created a form in excel with several checkboxes. There are several checkboxes in each cell. I need it to fit all on one page, but cannot do so because the font in the checkboxes is too big. Can I change the font size in the checkbox?
Posted by Susan at February 10, 2006 01:13
Not with these type of checkboxes that I'm aware of. (There is another type where the font can be changed, they work on a different principle though)

As a possible solution, how about using no text for the checkboxes? You can adjust the height of the row to show text in the cell above or below the checkboxes if necessary.
Posted by Andrew at February 10, 2006 10:49
I want to link two check boxes together. So if one is ticked the other is not, visa versa. I want to do this within VBA.
Posted by Simon K at March 02, 2006 10:27
Maybe easier to use Option buttons rather than Checkboxes?

If in the same group, clicking one on will turn the other off.
Posted by Andrew at March 02, 2006 20:36
how do i remove checkbox from excel. i did try all the 3 easy ways you mention above. but the check box on the sheet refuses to get selected. any answers?
Posted by Vijay S at April 13, 2006 23:26
Perhaps they are not Forms toolbar checkboxes? Try using the Control Toolbox toolbar, push the Design Mode button and try to select them. They are 2 different types of checkboxes and must be handled differently.
Posted by Andrew at April 14, 2006 00:16
Stupore! Amo questo luogo!:)))))))
Posted by aggiornare at February 19, 2007 07:51
Grazie! ;-)
Posted by Andrew at February 21, 2007 15:13
Hope someone can help me.
I designed a kind of checklist with checkboxes from Control Toolbar.

So far so good, but when I filter the checklist or suppress a few lines through groupings the checkboxes screw up and show themselves in fields where they shouldn't be.

What am I doing wrong here, or how could I influence the checkbos to know it shall only display when the specific row displays?

NB : I'm not familiar with VBA
Thanks !
Posted by Nicola at February 22, 2007 22:39