image_thumb.png

Enlarging Form Control Option Buttons

In a previous tip, I had complained about form control option buttons. I had argued they suffer because you can’t change the font size nor the “punch circle.” But then an idea dawned on me that builds off of that previous tip. We could use the same dynamic described in that earlier article and combine it with the camera tool.

Click here to learn more about the Camera tool.

Say Cheese!

Let’s take a look. On the left side, I’ve create a button list. This is just a place to store my option buttons. Notice I’ve placed them within a single cell each—this makes it easy to take a snapshot with the camera tool (since the camera will always only reference one cell).  The image below shows how  this works. Notice option buttons are all linked to cell B8, which I’ve named “SelectedIndex.”

image

Again, following my previous tip, we can assign each image created by the camera tool to a macro. That macro would change the Selected Index depending upon which button was selected

Click here to read that previous tip to understand it in full.

Let’s take a look at that macro:

Public Sub OptionButtonHandler()
    Dim Index As Integer
    Index = CInt(Replace(Application.Caller, "Button", ""))
    [SelectedIndex].Value = Index
    [SelectedIndex].Calculate
End Sub

You maybe wondering about that last line in the subroutine. Camera tool images can be a little funky. In the previous screenshot, you can see that these option buttons images are on the same tab as their form control counterparts. Presumably, you’ll place the option button images on a different worksheet tab and the original button list will live on a hidden tab. For whatever reason, whenever you place these form control images on another tab, the image won’t always sync with the right value. So making a call to recalculate will ensure that it does. The download file provided at the end of this post demonstrates a setup where the images are on a different tab.

Note: I’ve only noticed this problem in Excel 2010. Excel 2013+ does not seem to have this issue requiring a call to calculate. I’ve included it here so it’s backward compatible with previous versions of Excel. It’s also a bit slower, so feel free to remove it completely if it’s not an issue on your end. 

Discussion

Look, I’m probably not going to use these buttons anytime soon. I’m not saying you shouldn’t, but I don’t have a lot of use for them. I’ll still prefer my method of using shapes. However, the camera tool when combined with my earlier tip does allow you to create functionality not native to Excel. For instance, if you type a new font in one of the cells on the button list, you can even change the format of the radio button in the way it’s presented. In the image below, I’ve deleted the original caption to the option button. In its place I wrote another caption into the cell and picked a cheesy font. Yes, it doesn’t look great, but you can play with the formats and make it look better. This should give you an idea of what’s available.

image

My hope with this article is that we keep the creative juices flowing and see what other novel ways it can be applied.  Again, I probably won’t use this, but hey, maybe you will, and that’s enough for me.

I’m going to Dublin, Ireland

download

From December 23-30, I’ll be in Dublin, Ireland on holiday with my wife. If you are in the Dublin area and would like to say hi, let me know! Otherwise, this is likely my last blog post before the end of the year, so have happy holiday and terrific new year!

Download file: Large Option Buttons.xlsm

Also, buy my new book! Makes a great stocking stuffer!

Auto highlighting Excel charts

On your dashboards, reports, and charts it’s a good idea to alert the user to certain types of values. In a recent post, we discussed how to alert users to certain records on tables using custom formats. We’ll build upon that here but transition to how we might achieve a similar effect on a chart.

Take a look at the chart in the image below. I’ve built the chart so that no matter what values are being plotted, the greatest value is always highlighted in red. Following similar themes from previous blog posts, I achieved this effect with no VBA. The advantage to not using VBA is that it’s speedy and requires no iteration. On the other hand, for this effect to work, you’ll still need to test if every point is the maximum by using an Excel formula. For small series such as the one shown here, this isn’t really a big deal. But for series with many more points–and more more complex projects–it’s an additional column of data you’ll need to take care of.

image

Let’s do it!

Luckily, the implementation is super easy. So let’s get started. For this example, I needed to create some fake data. So for the values B2:B5, I used RANDBETWEEN. In cell B8, I then find the maximum value that has resulted. (Of course your data won’t be random, but to demonstrate this chart will always pick the greatest value, I use RANDBETWEEN.) The formulas for these items are shown in the following image.

image

Once we have the maximum value, we need to create a parallel series where that maximum value is the only value that appears. Everything else should be #N/A (or something similar–more on that in the discussion section). We could use zero here, but zero is actually a value on our chart. So it’s better we use something that represents a non-number. NA() is perfect for this. The image below shows an IF formula used in the adjacent column to test whether a specific region holds the maximum value.

image

Now, we should have two columns. The first column contains our original data; the second column contains only the maximum number from our original and #N/A’s. At this point, you’re ready to insert a chart. The easiest thing to do is to highlight our data range and insert a new column chart. I’ve done just that below.

image

Remember, the  values on my screen are being generated randomly, so the orange column may be at another range (or even another color) on your screen. At this point, what we’ll want to do is plot the “Max Series” –  the orange column – on the secondary axis. You can do this easily by right-clicking the orange column and going to Format Data Series…..

image

Within the Format Data Series dialog box (Excel 2010 and previous) or context pane (Excel 2013+) go to Series Options and select Secondary Axis. The image below will result.

image

All you have to do now is some clean up and then you’re finished. Below, you can see I’ve removed an axis and adjusted some formatting.

image

Discussion

In this simple example, we just used the maximum value, but you could highlight all sorts of values depending upon what you’re looking for. Perhaps you would like to highlight one or more columns whose values are in a specified range; or, perhaps you just want to direct your viewer to the smallest value. Really, the rules are endless but the dynamic is the same. You just need an additional column that parallels the first.

You may have noticed we introduced #N/A errors where a value did not match the maximum value. Generally, having unhandled errors on the spreadsheet can slow things down. (I’m not sure if that extends to generated errors like these, perhaps a reader could verify for me? I would think they make a contribution.) One way around this is to use a number like negative one instead. If you go that route, just make sure you’re only plotting positive data. You could also use a blank text string, “”. I’ll leave that choice up to you, but remember all options come with tradeoffs. Try each out and see which works the best for you.

Download file: Max Chart Highlight

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.