Better Option Button Controls

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. I’ve also used similar mechanisms in my new book, Dashboards for 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.

Advertisements

3 thoughts on “Better Option Button Controls

  1. Chris Macro

    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!

    Reply
    1. Jordan G Post author

      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.

      Reply
  2. Pingback: Enlarging Form Control Option Buttons | Option Explicit VBA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s