How to Create Single Select Checkboxes in Excel

Custom Checkboxes in Excel

Some time ago – pre “in-cell” checkboxes in Excel – I needed to provide a way for a client to select (attractive) checkboxes on an Excel worksheet. 

Let’s face it – the standard controls in Excel are antiquated and ugly. Beyond that, "ActiveX" controls are now being disabled by Microsoft due to security concerns.

Using a combination of rectangle shapes with some VBA code, I was able to provide the client with attractive, interactive on-worksheet checkboxes.

One reason for this approach, is that the client had a mix of Excel versions in house and many users did not have a version of Excel which supported the new checkboxes feature.

Custom Checkbox Creation

These checkboxes are created from rectangle shapes (the ones with rounded corners).

The “check” mark is created with the Segoe UI Symbol font although you can experiment with other fonts and symbols.

You can create any size, colour or shape for these checkboxes.

One reason to use this approach is for clients who do not have a version of Excel which supports the new checkboxes feature.

Excel in-cell Checkboxes

For a more recent project, I decided to experiment with Excel’s cell-based checkboxes.

In this example, I needed a way to allow the client to quickly select the data to import.

Here, the client could simply check or uncheck each category (or “Measure” in this example), they wanted to import.

It was fast and simple to implement with zero VBA code other than to determine the status of each category to import.

Another benefit is that adding more categories does not require modify the VBA code. The code simply loops through the options in the table.

Single Selection Checkboxes

But what if you only want one option of many selected?

Checkboxes don’t work as each checkbox is independent.

I had a project recently, where a client had an Excel table. Each row in the table represented a single choice and they want to be able to check a single row to process.

Essentially, I needed the checkboxes to work like option buttons (these are also known as "radio buttons").

To get this to work, I needed just a little VBA code to run when a checkbox was clicked.

This would uncheck all the checkboxes by setting the cell values to False. It then checked the clicked cell by setting its value to True.

Some VBA Magic

To get this to work, I needed just a little VBA code to run when a checkbox was clicked.

This would uncheck all the checkboxes by setting the cell values to False.

It then checked the clicked cell by setting its value to True.

While this was happening, Excel events were switched off to prevent the macro from triggering the macro.

As you can see, the amount of VBA code is minimal (there's more comments - in green - than code), yet it provides a very usable, interactive interface in Excel.

Conclusion

Over to you. Have you ever need the ability to select only one option in Excel without using Data Validation. How did you approach it. Leave your comments below.