October 17

Creating a Spreadsheet-Based Wizard

10  comments

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

Jordan Goldmeier
Latest posts by Jordan Goldmeier (see all)

Tags

optionexplicitvba


You may also like

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

October 26, 2014

Monte Carlo Simulation Formula in Excel – Tutorial and Download

September 17, 2012

The Excel Rollover Mini FAQ
    • Many apologies! I must have accidentally deleted the download link during an edit session. I’ll post the download file in a few minutes. Thanks for bringing that to my attention.

  • Hello I saw the podcast on Misterexcel yutube channel, the “roolover” trick it’s amazing it has huge potential ! Your blog is in my favorites now ! Thanks and keep up the good work.

  • I got hold of a German version of Visicalc on a project in Saudi Arabia in 1981 when I was a project controls manager on a construction project. I finally managed to get the English version, but still had to call after midnight to reach tech support in the U.S.A. to get directions on how to create a mapping script to make my HP 9-pin printer work! I’ve been using spreadsheets since, including Lotus and Borland products, and Excel 1 through 2013. Lots of good changes.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Free 24 Excel Tips Guide

    Discover the secrets to Excel efficiency with our free guide: '24 Proven Microsoft Excel Tips to Help You Save Time'

    A humble gift from us to you, to make your Excel journey smoother and more productive. Grab your copy now and start mastering Excel like never before!

    >