Category Archives: Spreadsheet Tricks

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!

Custom Formats for Dashboards and Spreadsheet Applications

Conditional formats are volatile. If you have too many, you see the effects of this volatility in serious calculations slowdown. As the function guru Charles Williams puts it, “…conditional formats seem to be super-volatile: they are evaluated each time the cell that contains them is repainted on the screen, even in Manual calculation mode….” But even too many non volatile functions can slow things down.

The good news is you have alternatives. For some specific instances you can get away with an Excel feature called Custom Formats, which is faster than conditional formatting and non volatile. It can also replace the IF and other conditional formulas.

However, you’ll deal with some major trade-offs. Custom formats as you’ll see can be confusing. And, because they’re not commonly used, others may not understand the work you’ve completed — and you yourself might even forget since custom format code is obscured away from the spreadsheet in a dialog box.

We’ll talk about those trade-offs and go through a few examples in this article.

Introduction to Custom Formats

Excel comes with an array of built-in custom formats. For instance, in the image below you can see I’ve placed a simple date on the worksheet. The representation of the date as month/date/year is in fact a custom format that Excel applies by default. You can see the format applied to any cell by selecting the cell and selecting More Number Formats from the format dropdown.

image

The image below shows custom code applied to the date on the spreadsheet. Notice initials like d and m stand in for things like day and month. These are part of the custom format codes that can be used to represent dates.

image

Conditional Expressions with Custom Formats

It’s the combination of these letters, numbers, and codes that allow you to make all sorts of customized formats. The codes in the previous image feature no conditional expressions; however, you can write conditions as formatting codes. The basic prototype for conditions is as follows: [Condition 1] ; [Condition 2]; [Else Condition]

Let’s say you apply the following condition code to a cell: [>10] “high”; [>5] “medium”; “low”.  The image below shows this code applied to the cells in column C. Column B simply equals the values in column C. Because the custom format is applied to column C, the values appear as the text we’ve outline in our formatting code. In C3, the value is 15; our custom format code tells Excel to represent this number as  “high.”

image

Notice cell B5 is simply set to be equal to cell C5 with a formula. In fact, all the cells shown in column B simply refer to the adjacent cells in C. The custom format acts as a mask for the actual underlying values. If you were to write =C3+5, the result value would be 20 (assuming you’ve switched the new cell to a General format) even as what’s being shown in C3 is text and not a value. Dates work in the same way: Excel stores dates as discrete whole numbers but they’re represented in a common date format. Custom formats only change how values are represented on a the spreadsheet; they does not affect underlying numerical values.

Custom Formats for Dashboards

Let’s apply what we’ve learned to a dashboard. Let’s say you want to create an alert where a colored dot character, ●, is shown next to over-budget project. The image below shows this effect. You would like a softer orange alert for projects $10,000 over budget and a harsher orange for projects even more over budget than that. The following image shows this effect achieved through custom formats.

image

To create this on your own, you would first figure out which projects are over budget in Column A. The image below shows we can do this easily by subtracting the amount spent from the budgeted amount.

image

Once this calculation is completed we can apply the following custom format to column A:

[Color46][>20000]●;[Color45][>=10000]●;[Color2]

So let’s break this custom format down. First, you might be wondering how to get the dot character. Well, you can copy and paste it from in this blog post. (That’s probably the easiest way!) Alternatively, you can insert it (or any other character) by going to the Insert tab and selecting Symbol. The dot resides in the Geometric Shapes subset. I usually insert the character to a random cell and then copy it from there, pasting it into the custom format as needed.

The next item to point out is the [ColorXX] code. Excel allows you to change the font colors depending upon the conditions satisfied. The numbers 45, 46, and 2 refer to color codes within Excel’s internal colors. Unfortunately, there are only 56 colors available, which is admittedly very constraining.  Click here to see the entire color list. (You might want to bookmark this link so you have the color table handy at all time.) For reference, Colors 46 and 45 are the dark and light orange respectively; Color2 is white.

Once you have the values in Column A as shown above, it’s a simple matter of selecting the values and applying the custom format. This is shown below with some resizing of column A to reflect how I want to see the dots. I’ve also increased the font size. Notice the custom format dropdown box on the ribbon now says “Custom” to reflect the custom format code we’ve created and applied.

image

Discussion

There’s much more to custom formats. For instance, the [>10] “high”; [>5] “medium”; “low” code from above could replace the following nested IF formula =IF(A1 > 10, “high”, IF(A1 > 5, “medium”, “low”)). The greatest advantage to custom formats however is that you can dynamically change how values are represented on the spreadsheet without the weight of additional calculation and volatility. Excel can instantly change how it represents an underlying value much faster than it can recalculate.

But as you can see custom formats are also pretty limited in formatting options; they’re sometimes hard to understand; and how they work isn’t always obvious (their code is hidden away in a dialog box). Indeed, for ninety-five percent of cases, the difference in calculation speed of an IF formula and the dynamic representation of a custom format is nominal.

Still custom formats present many interesting options for developers. For dashboards and models featuring heavy calculation, they prove to be a useful alternative.

Dashboards for Excel

My book Dashboards for Excel uses examples like this and so much more. Buy it today! (The book is out and shipping. Amazon is fixing the listing.)

Creating a Spreadsheet-Based Wizard

First of all, happy spreadsheet day! It was on this date today that VisiCalc was first shipped to the masses. The rest, as they say, is history.

So let’s talk about creating a spreadsheet-based wizard. By “spreadsheet-based,” I mean no Userforms. That’s right. There are many good Excel tutorials out there on developing wizards with UserForms, and I can certainly see their appeal. For example, if you are creating an add-in for Excel, then a wizard created with UserFroms is the best way to go.

However, if you are making a spreadsheet-based decision tool or dashboard, I say you should opt for a spreadsheet-based wizard. For one, I think you can make them more quickly. And second, UserForms can carry unnecessary bloat. Finally, I’ve found UserForms to be somewhat unpredictable in terms of layout across different monitors and resolutions. That said, a spreadsheet-based wizard might still look off on different monitors, but I think using the spreadsheet as a canvass makes this problem easier to prevent, diagnose, and fix.

So this is what a spreadsheet-based wizard looks like.

Spreadsheet Wizard

Pretty simple, eh? You’ll be amazed by how quickly you can throw what of these together.

So here’s how it works. Each panel or view is a named range of group of cells stretching across all rows. The Next and Back buttons simply show and hide these views accordingly. Below, I’ve unhidden each view in the example wizard and then zoomed out so their names are displayed. Note the naming and ordering scheme utilized. If I were to add another panel at the end, I’d call it Wizard.View5. This ordering scheme, as we shall see, is what makes these wizards so simple and easy.

image

Traversing the wizard

The Next and Back buttons employ pretty similar code. The Next contains a test to see if we’ve reach the end of our panel set; the Back contains a test to see if we’re at the beginning panel. There’s some extra stuff to test which view we’re in (View 2 requires some special instruction), but it’s all pretty simple.

Below, I’ve excerpted the procedure called when Next is pressed.

Public Sub GoNext()
Dim index As Integer
index = [Helper.CurrentPageIndex]
index = index + 1

If index > [Helper.TotalPages] Then Exit Sub
Wizard.Range(“Wizard.View” & index).Columns.Hidden = False

If index = 2 Then
DisplayCheckboxes
Else
HideCheckboxes
End If

If index > 1 Then
Wizard.Range(“Wizard.View” & index – 1).Columns.Hidden = True
End If

[Helper.CurrentPageIndex] = index
End Sub

 

The way we know and manage which view we’re currently looking at is through a helper cell on a Helper tab (the Helper tab is in the example file). This cell holds a number representing the index of the view we’re looking at. I’ve named this cell Helper.CurrentPageIndex. In addition, I’ve also stored a variable to keep track of total views, Helper.TotalPages. In this example file, I’ll update the total manually when I add a new view (by typing in the new total). But you could automate this process if you felt so inclined. In fact, you should.

In this example, Helper.CurrentPageIndex stores a number between 1 and 4 inclusive. If it equals 1 then we’re looking at the first view. When I press Next, the index is incremented and the next panel is displayed; the previous panel is then hidden. As you see from the code, I simply need to read in the index stored in Helper.CurrentPageIndex. This is why the numbering scheme is so great: the index is the only specific information required to act as a pointer to each view.

In fact, with this setup, my panels don’t even need to be in order. Of course, you should try to plan ahead so that you aren’t making view out of order – going out of order seems like a headache to me – but the option is there if you need. While I’m thinking about it, you could even change that last panel to something like Wizard.ViewFinished. Then you would make as many additional panels as you wanted thereafter. You would just need something in your code to test when you’ve reach that second to last panel so that users are directed toward Wizard.ViewFinished. It wouldn’t be hard, just one more IF statement. The numbering mechanism makes this part easy.

View Content

Editing each view is also fairly simple. If you want to see all views at once, you can simply unhide everything. When you’re ready to hide them again, pressing the Next and Back buttons in each direction should bring you back to hiding/showing each view as necessary. (In the example file, try unhiding everything and then pressing the Next and Back buttons – you’ll see what I mean.)  And, just like with UserForms, you place inside the view what you want for Excel to display when it’s in view. For input cells, it’s just a matter of creating a border around a cell range. Pictures and shapes do require the extra step of selecting the Move and size with cells option from within the properties settings.

image

The only tricky items are form controls, which are sometimes really weird about sizing. This weirdness is only confounded by Excel 2013’s properties menus. Take a look at the picture below. If you’re looking in the Format Control dialog box (on the left), the Move and size with cells option appears disabled. Now compare that to Excel 2013’s new properties pop-up thingy on the right. The option is now available.

image

In any event, form controls seem to become displaced across the different resolutions and dpi settings of individual machines. Sometimes they’ll get caught overlapping between two different view panels. When this happens, funkiness ensues. The form control’s size gets mangled. Sometimes Excel ends up copying that form control to that same spot over and over again. You find that you have 10 different checkboxes with the same name. If this has happened to you before, then you know the frustration. For the dubious who’ve never experienced this problem, it’s real. I’m not making it up. I swear.

So your best defense when using form controls on ranges that you intend on showing and hiding is to anchor each control to certain place on the spreadsheet. In this case, I’ve defined a section of cells and called them Wizard.CheckboxAnchor. I’ve also named each check box something like Check1, Check2, Check3 … etc.

image

When I want to view these checkboxes, I’ll call the DisplayCheckboxes procedure. In my Back and Next buttons, I’ve incorporated a test in the GoNext and GoBack procedures to show/hide the checkboxes for each view accordingly. For example, we only need to see these checkboxes when the second step is in view. Scroll up to the code listing view this test. Below, I’ve excerpted the DisplayCheckboxes procedure which is called when Step 2 is in view.

Private Sub DisplayCheckboxes()
Dim i As Integer

For i = 1 To [Wizard.CheckboxAnchor].Rows.Count
Dim currentCheckbox As Excel.Shape
Set currentCheckbox = Me.Shapes(“Check” & i)
With [Wizard.CheckboxAnchor].Rows(i).Cells
currentCheckbox.Width = .Width
currentCheckbox.Height = .Height
currentCheckbox.Top = .Top
currentCheckbox.Left = .Left
End With
currentCheckbox.Visible = True
Next i
End Sub

 

Notice how this code is very  similar to the view controller mechanism from in the GoNext procedure. Again, I group similar spreadsheet objects together by name. I use the suffix as an Id.

Information Panes

I use some conditional formatting in the left information pane to highlight which step I’m looking at. And, on the right, I have a bit of dummy text that would otherwise act as instructions. Take a look at the formula below. I’m not using any VBA directly to change the Instruction text. In this setup, instruction text for each step is stored in the Helper tab in the Instructions Table.

image

Thoughts

I like this method for creating wizards because it’s quick to cook up. Once you’re pleased with your design, you can save it as a simple template. Later it becomes a boilerplate from which to create different wizards for your different projects. You’re also not limited to this type of layout, of course. You could place the information panes on the top instead – you would hide/display rows rather than columns. Personally, I’ve used both and prefer the layout employed in this example.

Left for you to do

I’ve created a simple example that does require more work before it can be deployed.

Specifically, for each panel, it’s a good idea to activate the first cell or input item. This will always place the selector in view. Second, the input items in this example aren’t linked to anything. You’ll want to link these to some backend database or spreadsheet tab devoted to storage – especially if you expect a user to use the wizard several times over to create a list of items. You’ll also want to clear out data entry each time you start over with the wizard. Finally, it might be a good idea, when testing for certain view in the GoNext and GoPrevious procedures,  to use constants instead of literal numbers. I leave these taskings to you.

One last thought, I promise. As you’ve probably noticed, I’ve moved away from Hungarian notation for naming spreadsheet objects. The idea of using a “dot” for named ranges came from a blog post I had read on Charley Kyd’s blog. I’ve tried really hard to find that article again, but I can’t seem to locate it. (So the former link just goes to his blog home page.) In any event, I think it’s a good idea to name your wizard items following this nomenclature. In the input section of the first panel, consider giving the First Name input a name of something like “Wizard.View1.FirstName,” or “Wizard.Introduction.FirstName.” I find when you refer to named ranges like these in your code – and even in formulas – they’re so much easier to read and understand than something like “valFirstName.”

Happy spreadsheet day.

Here’s the download file:

Wizard Example

Directly link Excel form controls to backend data with dynamic references

Form controls are great for reporting information about groups of items, like a list of programs or accounts. They are often used on Excel dashboards and reports that demand interactive capabilities. One such type of capability provides the user with a list of items to choose from. When the user makes a selection, a macro is executed that populates a table holding referenced values. Those values are linked to a series of form controls on the frontend. This interactivity is displayed below:

Step 1
Step 2

Step 3

There is a final step, which I haven’t included. The user would make changes to the project under the Options table. They would press a “Save” button and their changes would be copied from the Linked Values table back onto the backend data in the column corresponding to the selected project using VBA.

The No-VBA way

There’s nothing wrong with this method in and of itself, but I want to propose a method that requires no VBA. The advantage of this new method is that it links directly to the data itself and bypasses the need for the Linked Values table. We can do this by allowing the form controls to take advantage of dynamic references.

Typically, form controls can only do direct, absolute references. You cannot, for example, use VLOOKUP or INDEX within the source field of a form control. However, you can use a named ranges.

Let’s do it!

First, we give that ‘index’ field above a named. How about selection? Next, we create four named ranges to correspond to the form control checkboxes. Stage_1 to Stage_4 are those new named ranges.

As you can see from the picture, I use the fourth row to connect to checkbox Stage 4 and the selection value to inform Excel to pull from the fourth column in the backend data (which is Project 4, if you recall).

Finally, I can simply link these named ranges to their associated checkboxes:

Using this method, changes to the checkbox automatically change the backend data. There is no intermediate table required — like the Linked Values table above — to interface between the frontend and the backend.

That’s all for now – have a happy and health holiday season!

Update 25 December:
Make sure to see the download file – Direct Links.xlsm.