Creating a Spreadsheet-Based Wizard

by Jordan Goldmeier

Today, 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 UserForms 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.

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.

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.

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.

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.

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.

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 decisions 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.”

Here’s the download file:

Wizard Example

About the Author

Jordan Goldmeier is an internationally recognized data science/analytics professional and visualization expert, author, and speaker. He offers a free 3-part video series on building dashboards in Excel at Excel.tv, owns the data consulting agency Cambia Factor, and is the author of Dashboards for Excel, and Advanced Excel Essentials, both published by Apress. He has consulted with and provided training for NATO, the Pentagon, and Financial Times among others, His work has been cited by and quoted in the Associated Press, Bloomberg BusinessWeek, Dice News, and American Express OPEN Forum. Jordan has been named as a Microsoft MVP since 2013.

Want more? Find more in depth information in Dashboards for Excel by Jordan Goldmeier and Purnachandra Duggirala, ISBN: 978-1-4302-4944-3.