November 29

Better Option Button Controls

6  comments

So let’s take a look at your standard form control option buttons. In the image below, I have three buttons linked to cell F3. Cell F3 shows a three since the third option button has been selected.

image

But I’m not in love with option buttons mostly because I can’t really do a whole lot of formatting on them. Want a larger font? Tough. Want to make that circle bigger? TOO BAD. Plus, there’s the fact that they remind me of punch card voting ballots. They just look old. (See Chapter 5 of Advanced Excel Essentials for why I also despise ActiveX controls.)

So here’s an alternative. I like to use shapes instead. In the following image, I’ve replaced the option buttons with shapes and have assigned a macro to each of them.

image

I know what you’re thinking. Using form control option buttons is super easy; the method I’m presenting requires macros and named ranges. Why make things more complicated?

Well, it’s all about tradeoffs, isn’t it? This method is actually surprisingly easy to implement. I believe the small amount of code required is a good trade off for having more control over layout and format. And, if nothing else, I present new options to you, the developer. But mostly, it’s just super easy to implement. Once you see how this construction works, you’ll also see there are many other applications for it, most of which cannot be so easily replicated with form controls.

The Basics

The first step is to treat your shapes as a series. That means each shape is part of a range of options. Take a look at what I’ve done below. The name of the button with the caption “Option 1,” is simply Option1, “Option 2” is named Option2, and so forth. For this example, I’ve used captions like “Option 1” to represent the first button, also named Option1. But these buttons can have whatever caption you’d like. What important for this dynamic is that the buttons share a consistent identity. They could just as easily be named Button1, Button2, and Button3.

image

In addition, I’ve named cell E2 LinkedCell for easy access within our code (see below).

image

Once the buttons have been laid out, they  can be assigned to a macro. For this example, I’ll assume you want to assign them to separate macros. But as you’ll see in the code, this dynamic easily lends itself to having each button go to one centralized macro. (We’ll go through a centralized “handler” macro design in the next section.) Assuming you’re working with with three different macros, the could for the three option buttons would be as follows.

Public Sub Option1Selected()
[LinkedCell].Value = 1
' Do some stuff here
UpdateDisplayOfSelectedOption
End Sub

Public Sub Option2Selected()
[LinkedCell].Value = 2
‘ Do some stuff here
UpdateDisplayOfSelectedOption
End Sub


Public Sub Option3Selected()
[LinkedCell].Value = 3
' Do some stuff here
UpdateDisplayOfSelectedOption
End Sub

Each of these buttons does essentially the same thing: they write the number applied to the suffix of each button to the spreadsheet. Notice this behaves exactly like form controls. In the last line of each procedure, I call UpdateDisplayOfSelectedOption.The code for this procedure is as follows:

Public Sub UpdateDisplayOfSelectedOption()

Dim CurrentIndex As Integer
Dim LinkedCellIndex As Integer

LinkedCellIndex = [LinkedCell].Value

For CurrentIndex = 1 To 3
With Me.Shapes(“Option” & CurrentIndex)
If CurrentIndex <> LinkedCellIndex Then
.Fill.ForeColor.RGB = RGB(242, 242, 242)
Else
.Fill.ForeColor.RGB = RGB(248, 203, 173)
End If
End With
Next

End Sub


The code here is pretty straightforward. We set up an iterator (CurrentIndex) to go through all shapes beginning with the name “Option.” If the CurrentIndex equals the LinkedCell, we know we’re dealing with the cell that was selected, so we’ll give it a special color to set it off. Everything else will be the same color.

Leveling Up

The basic structure has been setup, and we could stop here if we wanted. But I think we can go a little further if only to make our code a little less explicit. Right now, it’s filled with numerical constants, generally considered not the best way to go. We could make one procedure that serves as the clearinghouse for all buttons.

We’ll need to take the added if unfortunate step of keeping track of how many buttons exist on our spreadsheet (shown below as TotalButtons). This added step might feel like an annoyance at first, but you’ll see it actually helps in keeping track of your work as it grows. As you add or remove a buttons in your development process you’ll simply type in the updated count into the cell.

image

Now let’s take a look at the code updates.

Option Explicit
Const LIGHT_GREY = 15921906 ‘ The result of RGB(242, 242, 242)
Const PEACH = 11389944 ‘The result of RGB(248, 203, 173)

Public Sub ButtonHandler()

Dim CurrentIndex As Integer
CurrentIndex = CInt(Replace(Application.Caller, “Option”, “”))
[LinkedCell].Value = CurrentIndex

Select Case CurrentIndex
Case 1:
‘…
Case 2:
‘…
Case 3:
‘…
End Select

UpdateDisplayOfSelectedOption
End Sub

Public Sub
UpdateDisplayOfSelectedOption()

Dim CurrentIndex As Integer
Dim LinkedCellIndex As Integer

LinkedCellIndex = [LinkedCell].Value

For CurrentIndex = 1 To [TotalButtons].Value
With Me.Shapes(“Option” & CurrentIndex)
If CurrentIndex <> LinkedCellIndex Then
If Not .Fill.ForeColor.RGB = LIGHT_GREY Then .Fill.ForeColor.RGB = LIGHT_GREY
Else
If Not .Fill.ForeColor.RGB = PEACH Then .Fill.ForeColor.RGB = PEACH
End If
End With
Next

End Sub


Notice we’ve replaced the separate procedures with one procedure. Application.Caller returns the name of the button that called the procedure. So if it’s called by clicking on Option1, then Application.Caller will return Option1. Since we know all potential buttons in our series start with the name “Option,” we can use the Replace function to replace the term “option” with nothing. This will just leave us with index of the current option selected. I include CINT() to convert the resulting string to an integer, but I don’t think it’s truly necessary. I think Excel will successfully make the conversion on its own. But it’s not a bad idea to remind yourself what’s happening in the code by including it.

Two small enhancements were made to the UpdateDisplayOfSelectedOption procedure. First, the RGB functions were replaced with named constants. Second, we test if the button is already set to a specific color before setting it to the same color again. This technique was described in my favorite book Professional Excel Development.  Consider how testing properties first might prove useful. In every instance, there’s only one button that needs to be recolored—it’s the prior selected button. So instead of recoloring every single button, we only recolor the last button selected. This means we don’t have to keep track of the previously clicked button even if that’s all that needs to be changed. This speed benefit for checking a property before changing it can be significant in larger applications.

Discussion

This may seem like a trivial example, but consider how the dynamic can be used. I use a similar dynamic in a previous article, A Dynamically Linked Checkbox Matrix with Microsoft Excel.

What did you think? What ideas do you have for creating better looking form controls?

You can download the file used in this article by clicking this link.

Jordan Goldmeier
Latest posts by Jordan Goldmeier (see all)

Tags


You may also like

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

October 26, 2014

Monte Carlo Simulation Formula in Excel – Tutorial and Download

September 17, 2012

The Excel Rollover Mini FAQ
  • Love this article! Another idea would be to use a circle shape in combination with a textbox that changes color when selected — or better yet, give your circles a white fill with a black border and the have the selected one have a fill color in it. So many more options with this kind of solution!

    • It’s true – the possibilities are endless! I had trouble really trying to keep this article simple while planting the seed on what one could do.
      Another idea is that you can use the camera tool and take pictures of all your option buttons separately. Then you can enlarge those pictures. You would name each picture following the same numbering scheme described above and then change the value in the linkedcell based which associated picture of an option button was clicked.

  • Hi, really love this solution. Is there any way to execute this macro separately multiple times on one sheet? For example if I use it in a survey where I have 10 questions and 3 options for each question? Thanks a lot! 🙂

    • Sure. The way to do that is to go back to the numbering scheme. Right now this example only deals with three options. But let’s say as in your example you have 10 questions with three options each. Here’s what you do: the first row of boxes could be named Check11, Check12, Check13. The next row would be Check21, Check 22, Check 23. In the code, you’d still do something like this:
      Index = Replace(Application.Caller, “Option”, “”)
      CurrentRow = CINT(Left(Index, 1))
      CurrentSelection = CINT(Right(Index, 1))
      So that would let you know which set of questions you’re dealing with.
      When you iterate to recolor each button, you would only want to do it to a specific row, so you might update the with in the FOR loop to:
      With Me.Shapes(“Option” & CurrentRow & CurrentIndex)
      I’ve not really tested any of this code but I think you get the idea: use the names of the checkboxes as your guide.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Free 24 Excel Tips Guide

    Discover the secrets to Excel efficiency with our free guide: '24 Proven Microsoft Excel Tips to Help You Save Time'

    A humble gift from us to you, to make your Excel journey smoother and more productive. Grab your copy now and start mastering Excel like never before!

    >