Category Archives: Tips

Advanced Excel Essentials for $10

EDIT: Unfortunately, the deal has ended.

I’m writing this from my car on my way to visit family so forgive the brevity and potential spelling errors. Thanks to fellow Apress author, Debra of Contextures.com, for letting me know! You can get my first book in ebook/pdf format for $10.00. Follow this link to get your copy: http://www.apress.com/9781484207352

 

Advertisements

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!

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.

Dude, where’s my book?

Yes, it’s delayed, but hopefully not for long. Apress has decided to use their new cover design for the book. That means the new cover needs to get approved by the higher-ups, which has taken some time. But once approved, the book goes to print.

Here’s that new cover design, by the way. Pretty snazzy, eh?

11011113_10101481916210226_8283917222203818064_n