Check boxes are supposed to be used to allow multiple selections. But since they are now so easy to insert, you might want to use them to allow a single selection. Here’s one way to do that.
In the image below I have a chart from a blog post last year.
One of the columns has been highlighted via clicking a checkbox in column D.
But as you can see below you can also highlight another column.
If you only want one column to be highlighted, you can use Excel’s data validation to stop more than one check box being clicked.
Make sure only one or no check box is selected.
With the range D2:D7 selected press in sequence Alt D L.
In the dialog that opens, click the Allow: drop down and select Custom.
In the Formula: box enter the following formula.
=COUNTIF($D$2:$D$7,TRUE)<=1This formula ensures only one check box can be selected at a time.
You now must uncheck the selected check box before you can select another check box.
Nice method… I like it! Now for the downside. I posted a VB6 program for this treatment for checkboxes many, many years ago in a Visual Basic (not VBA) mail list (the predecessor to Forums for the young’un out there) and received negative feedback for the effort. It seems people (at least back then) did not want to see the Microsoft standard broken… for them, option buttons needed to act like option buttons and checkboxes needed to act like checkboxes and they thought it was confusing and unnatural to force one act like the other. I never pursued the method after that. Just thought you would find that history interesting.
Hi Rick
Noted – I am still on an Excel Mail List – not many posts these days.
I have seen a few recent surveys where they have used check boxes but only want one answer, so not sure if the lines are blurring.
Mail lists (any subject) still exist??!!?? I thought they all went away years ago. The only ones I think I knew about were all hosted on Microsoft’s server which they closed down many years ago now.
Hi Rick
I am still on the Excel-L list – not many posts these days – maybe one a fortnight or a month.
I am on the daily digest and it used to be daily.