This chapter begins the second half of the book. From this chapter and on, you’ll be creating a spreadsheet-based application using many of the principles discussed in the first few chapters. To get an idea of what you’re building, you can download the completed version, Chapter20Finished.xlsm, from within this book’s project files.

In the next four chapters, I will present you with completed work whose functions you’ll reverse engineer. In that way, you’re going to apply the principles from the previous chapters (as well as learn a few more along the way).

There are two good reasons for this teaching style. First, in the real world, you won’t always start from scratch. Sometimes you’ll receive work built by someone else. You have to reverse engineer what they’ve completed and also add your own features. Many of the examples files going forward are much like that inherited spreadsheet. You should know how they work, but I also want you to think creatively of how they can be extended (and tailored) for your use.

The second reason goes back to the phrase mentioned in previous chapters—that of reusable components. Many of the features I’ll describe are not steps in a larger spreadsheet. Rather, they exist in their own right. They’re as applicable here as they are for other spreadsheet projects. Recall from the introduction I said the most important skill to succeed in this book is creativity. That creativity will help you understand how to implement these components in your work.

The bulk of this chapter deals with creating a spreadsheet-based input wizard with Excel. But before diving into the wizard, I’ll discuss creating simple spreadsheet-based forms and why they’re often the better choice compared to UserForms. From there, you’ll start with a completed version of the spreadsheet-based wizard. I’ll walk you through several of the design components, including proper layout, input pages, and features of the user interface. By the end of the chapter, you should see how building a spreadsheet-based input wizard is consistent with building faster and leaner Excel applications.

Of Input Forms and Excel

Most Excel developers would prefer UserForms to capture user input, especially when the user input has multiple steps. Indeed, conventional wisdom often argues for using UserForms and ActiveX controls. The problem is that ActiveX controls can be somewhat finicky and unpredictable, as established in Chapter 16. Remember this figure from Chapter 16 (now called Figure 17-1)?

UserForms are a type of ActiveX control and they suffer from the same unpredictability. For instance, UserForms will sometimes appear different across different computers. This is the result of different internal settings and hardware. Monitor resolution, DPI, and Windows’ internal font default can potentially cause these unwanted effects.

One way to get around all of this is develop input forms directly on the spreadsheet. This is what I advocate. It may seem like a hard task at first, but you will soon find it provides flexibility not found when using UserForms. In addition, the spreadsheet provides a better canvas upon which to create a more aesthetically pleasing experience. The dull grey scheme that appears by default in the UserForms feels almost anachronistic in this day and age, a relic of a bygone era. Figure 17-2 shows an example UserForm I pulled from Microsoft’s Developer Network’s help pages.

Let’s take a look at what you can do when you create input forms on the spreadsheet instead.

A Simple Input Form

In this section, I’ll discuss how to create a simple input form. Open Chapter17SimpleInput.xlsm to follow along. Figure 17-3 is a snapshot of the input form in Chapter17SimpleInput.xlsm.

You can create a new input form in Excel with nothing more than an unused worksheet tab. With an idea of the information you’d like to collect at hand, it’s a simple matter of laying everything out.

Nothing too fancy goes into creating something like this. Each input box is simply a named range. If you’ve ever created an input form on UserForm before, you know that each input TextBox is given a name. For instance, convention would tell us the name for TextBox on a UserForm that stores a Project Name would go by txtProjectName. You’re doing a similar action by name each cell with a named range. The named range, as you shall see, will give you easy programmatic access to the cell’s value later on down the road. Figure 17-4 shows the named ranges and the input cells they point to.

The green checks and red x glyphs in Figure 17-4 serve as data validation indicators. You probably don’t need one for each and every box, but there may be inputs you want to specifically point your users’ eyes toward completing. There’s no fancy coding required to create these. In fact, they require no VBA code at all. It’s just a simple formula and some custom formatting. Take a look at the formula in Figure 17-5.

Here, you’re simply testing whether the length of the text entered in the adjacent cell is greater than one. If it is, that means something has been written in the cell. If the length of text is zero, that means no input has been provided. Recall that the double-dash is shorthand for converting the Boolean values of True and False into zero and one.

If you take a closer look at Figure 17-5, you’ll notice that the formula in the cell is not readable text. The reason is because to get the checkbox and x symbols, I used the Wingdings 2 font.

Custom Formats for Input Validation

In this section, I’ll talk about how custom formats can help turn those zeros and ones into x’s and checkmarks. It’s simple; you use custom formatting. In Figure 17-6, I’ve used the custom formatting syntax to tell Excel what to display when the number is either a one or zero.

So let’s break this down. With custom formats, I can create conditions to let Excel know when to display which symbol. For example, I have two conditions in the above formula. Can you guess what they look like? If you notice [=1] and [=0] then you’re spot on! These blocks of syntax outline the conditions. Note that the semicolon separates each condition.

Now take a look at the two character symbols that are being returned. There’s a “P” and a really weird looking “Д thing. To get these characters, I actually looked them up using the Symbol dialog box from on the Insert tab (see Figure 17-8). In this case, I selected Wingdings 2 as the font and inserted into Excel the symbols I desired. When Excel inserts these symbols into the worksheet, they’ll be in the Wingdings 2 font.

But if you look again at Figure 17-6, you’ll see the input box in the Format Cells dialog box is looking for regular alphanumeric characters—not symbols. So you’ll need to get those Windings 2 symbols back into regular text. The easiest way to do this is to select the cell in which you’ve inserted the symbol and change it to a normal font, like Arial, Calibri, or Times New Roman. Figure 17-7 demonstrates what happens when you convert the output from Wingdings 2 to Calibri.

Finally, you’ll notice the other two syntax blocks in Figure 17-6 that look like [ColorXX], where XX is some number. The XX in this case is in fact a number that points to a specific color index. To see a full list of colors to choose from, go to http://dmcritchie.mvps.org/excel/colors.htm.

The basic syntax for custom formats used here is [Color XX][condition]<symbol to return>. There are other format options available, and I encourage you to take a look at them. But they are beyond the scope of this book.

Based on what you’ve learned so far, you’re now ready to begin building a spreadsheet wizard to take input from the user. Notice that this simple input form can be created rather quickly and uses only formulas. The same form would take longer to create if made on a UserForm.

Creating a Spreadsheet-Based Wizard

In this section, you’ll build off the input form created from the previous section. However, you’ll also spend considerable time on the layout mechanics of a spreadsheet-based wizard. As stated in the beginning of the chapter, you’ll focus on components rather than building from scratch. I recommend following along by opening Chapter17Wizard.xlsm from within the project files.

In Figure 17-9, you can see the beginnings of a spreadsheet-based wizard that will serve as the backbone for the spreadsheet application you complete in forthcoming chapters. If you have Chapter17wizard.xlsm open, I recommend going through all the interactive components.

For instance, a user can use the back and next buttons (Figure 17-9) and the current page in the middle will change to reflect the choice. Figure 17-9 shows the Introduction page of the wizard.

Figure 17-10 shows the screen for the second page after pressing the Next button on the first page.

Layout Patterns for the Spreadsheet-Based Wizard

This section discusses the proper spreadsheet layout required to create a spreadsheet-based wizard. If you look closely at the difference between Figure 17-9 and Figure 17-10, you’ll see that the column headings have changed in the center view. This is because the first view referred to a different set of columns. When you pressed Next, it hid this set of columns and advanced to the next set of columns. Figure 17-11 shows all of the panes built into this wizard by unhiding the entire sheet. Notice that they are laid out from left to right an incrementally increasing order.

The mechanism shows and hides these columns accordingly. If you unhide everything and then zoom out, you can see each of these views laid out accordingly.

Note that I’ve named these views successively: View1, View2, View3, etc. In this setup, it makes it easy to know which view you are currently on. As well, you can know the successive panes in the list in either direction, whether you go forward or backward. Consider, if you were on View2, you’d know the previous screen would be View1 and the next screen would be View3.

Think about the ease of this setup. If you want to make changes to each step, you simple need to make them in that step’s set of columns. If you’d like to add another step, you could insert another series of columns in front of Wizard.View4 and name it Wizard.View5. The Name Manager can help you keep track of how many views you have (see Figure 17-12). In addition, you can jump to the step you want automatically by selecting its name.

The Helper Tab

In this section I’ll talk about the Helper tab (see Figure 17-13), which is an integral part of the spreadsheet-based wizard.

As has been the case with previous spreadsheets, I always suggest placing extra information either in a hidden spot on the spreadsheet or in another tab. In this case, you have several items in the Helper tab (see Figure 17-14).

In Figure 17-15, cell B1 has been given the name Helper.CurrentPageIndex. Cell B2 has been given the name Helper.TotalPages. Note that Helper.CurrentPageIndex keeps track of the current page in view. Its value is changed within the code. Helper.TotalPages is manually updated (that is, by you, the human) when you add new views. You could automate the process of ensuring Helper.TotalPages always has the correct total views. For now, I don’t foresee you adding additional views, so let’s keep it as is.

Going back to the Wizard tab, you can see that Helper.CurrentPageIndex is referenced to let you know what page number you are on (see Figure 17-15).

Moving Between Views

For your wizard to have its full effect, you need a way to move back and forth between the views. That’s what the Next and Back buttons on the wizard help you do. The following code listings show the code that is called when you press forward (Listing 17-1) and backward (Listing 17-2).

Public Sub GoNext()
    Dim index As Integer

    ' Read in the current page index and increment it by one
    ' to go next
    index = [Helper.CurrentPageIndex]
    index = index + 1

    ' Check if we're already on the last page
    If index > [Helper.TotalPages] Then Exit Sub

    ' Unhide the next view
    Wizard.Range("Wizard.View" & index).Columns.Hidden = False

    ' Check to see if we're on a page that requires special instructions
    If index = 2 Then
        DisplayCheckboxes
    Else
        HideCheckboxes
    End If

    ' Hide the current set of columns
    If index > 1 Then
        Wizard.Range("Wizard.View" & index - 1).Columns.Hidden = True
    End If

    'Set Helper.CurrentPageIndex equal to the next page index
    [Helper.CurrentPageIndex] = index
End Sub

Public Sub GoPrevious()
    Dim index As Integer

    ' Read in the current page index and decrement it by one
    ' to go previous
    index = [Helper.CurrentPageIndex]
    index = index - 1

    ' Check if we're already on the first page
    If index < 1 Then Exit Sub

    ' Unhide the previous view
    Wizard.Range("Wizard.View" & index).Columns.Hidden = False

    ' Check to see if we're on a page that requires special instructions
    If index = 2 Then
        DisplayCheckboxes
    Else
        HideCheckboxes
    End If

    ' Hide the current set of columns
    If index < [Helper.TotalPages] Then
        Wizard.Range("Wizard.View" & index + 1).Columns.Hidden = True
    End If

    'Set Helper.CurrentPageIndex equal to the previous page index
    [Helper.CurrentPageIndex] = index

End Sub

Take a look through both listings. Notice that they are very similar except for a few minor differences. The GoNext procedure checks to see if you’ve reached the end of the set of views while the GoPrevious procedure checks if you’re still at the beginning. The GoNext procedure increments the current page index, while the GoPrevious procedure decrements the current page index. This is another example of a reusable component—the mechanism to go forward and backward is virtually the same, so you just need to make a few accommodations. If you think about creating a general mechanism, then reusing and adjusting the code is easy.

Views That Require Additional Instruction

Some views require extra instruction before they’re displayed. For example, Figure 17-16 shows a series of check boxes, which require additional explanation.

Unlike input cells form on other views, the check boxes are form controls (CheckBox). They sit on top of the spreadsheet. It’s not enough to simply hide the form controls by hiding the view on which they reside. The reason is that form controls don’t always become hidden so cleanly when you hide a column, even when you set them to move and size with cells in their properties. So you may be wondering how to ensure that these check boxes always appear in the correct location. The answer is a technique I’ve come up with called anchoring.

Anchoring Controls

In this section, I’ll talk about how to anchor your controls so they always appear in the same spot when you hide and unhide columns or rows. The first thing you need to do is name your desired controls as part of a series. Let’s go back to that second view. Figure 17-17 highlights the first check box in the series.

Notice that the name of the check box is Check1. The check box below it is named Check2, and below that is Check3, all the way through to Check8. Furthermore, in Figure 17-18, I’ve selected the range that appears under each check box. Notice I’ve named it Wizard.CheckboxAnchor. This anchor will be your guide in placing these check boxes.

Now recall this snippet of code from GoNext and GoPrevious, shown in Listing 17-1 and Listing 17-2. When you are showing the second view, View2, you call the procedure DisplayCheckboxes; when you leave the second view, you call the procedure HideCheckboxes. Listing 17-3 excerpts this code.

' Check to see if we're on a page that requires special instructions
If index = 2 Then
    DisplayCheckboxes
Else
    HideCheckboxes
End If

Now let’s take a look at the DisplayCheckboxes shown in Listing 17-4.

Private Sub DisplayCheckboxes()
    Dim i As Integer

    'Iterate through each cell in our anchor
    For i = 1 To [Wizard.CheckboxAnchor].Rows.Count

        'Create a shape object to point to our current Checkbox
        Dim CurrentCheckbox As Excel.Shape
        Set CurrentCheckbox = Me.Shapes("Check" & i)

        'Set the checkbox to be the exact same size as the
        'as the cell it sits atop
        With [Wizard.CheckboxAnchor].Rows(i).Cells
            CurrentCheckbox.Width = .Width
            CurrentCheckbox.Height = .Height
            CurrentCheckbox.Top = .Top
            CurrentCheckbox.Left = .Left
        End With

        'Ensure people can see it
        CurrentCheckbox.Visible = True
    Next i
End Sub

In this code, you iterate through every cell that constitutes your anchor. For your purposes, the iterator i not only helps you track your current location through each anchor cell but it also helps you reference the corresponding check box.

You’ll notice that I reference each check box through the spreadsheet’s internal shape container. When you treat check boxes as shapes, you are exposed to the properties that are only available to a shape object. This helps because the check box object does not always show its properties and methods with IntelliSense (more on that later in the chapter).

In the line With [Wizard.CheckboxAnchor].Rows(i).Cells, you are grabbing the current cell in your anchor given at index i. With that current cell, you can tell the check box with the same name given by index i—that is, if you are on cell 1 in Wizard.CheckboxAnchor, use the check box with the name Check1. You then tell that check box to be the exact same width and height, and the same top and left. This ensures the check box takes up the entire width of any cell in your anchor. You can see this effect in Figure 17-17.

When you’re not on the second view, you’ll want to hide these check boxes. Listing 17-5 shows how you do just that.

Private Sub HideCheckboxes()

    Dim i As Integer

    'Iterate through each cell in our anchor
    For i = 1 To [Wizard.CheckboxAnchor].Rows.Count

        'Create a shape object to point to our current Checkbox
        Dim CurrentCheckbox As Excel.Shape
        Set CurrentCheckbox = Me.Shapes("Check" & i)

            CurrentCheckbox.Top = 0
            CurrentCheckbox.Left = 0
            CurrentCheckbox.Width = 0
            CurrentCheckbox.Height = 0

        'Ensure the checkbox is no longer visible
        CurrentCheckbox.Visible = False

    Next i
End Sub

Just as GoPrevious was similar to GoNext, but in a different direction, HideCheckBoxes is very similar to DisplayCheckboxes. It simply undoes the work performed in DisplayCheckboxes.

But you may be wondering, is it even necessary to change the height, width, top, and left if you’re just going to hide the check boxes? The truth is, it may not be. You could simply hide these check boxes without doing anything else. At least, at a product level it makes no difference. However, while developing anchors on your spreadsheet, moving every unused check box to a safe location is a good idea.

Here’s why. Excel acts somewhat unpredictably when working with form controls. If the above code errors out because there was a bug in the original loop, you might notice the check boxes didn’t disappear as they should have. Sometimes, Excel will make several copies of the same CheckBox control (one on top of the other). What causes this is an error in your code while working with multiple form controls. By moving each control to a safe location, you can monitor when Excel has made copies of itself.

Anchoring for Large Sets of Controls

In the previous section’s example, one could easily insert eight check boxes and then name them accordingly. It’s not necessarily the most enjoyable of exercises, but it’s a simple and quick task. What happens if you have so many controls that this take becomes incredibly burdensome? In this section, I’ll talk about a quick method of anchoring for large regions.

In Figure 17-19, I’ve created a large check box anchor region, which I’ve highlighted in gray for demonstration purposes. Like the anchor region above, I’ve made this region a named range.

You can quickly create enough check boxes for this entire region by reusing elements of the above presented code. Listing 17-6 shows the code you can use to quickly fill up the entire region with check boxes.

Public Sub FillCheckboxAnchorRegion()

    'Clear out any checkboxes already created.
    'This will ensure we don't duplicate checkbox
    'names.
    Me.CheckBoxes.Delete

    Dim i As Integer

    For i = 1 To [CheckboxAnchor].Rows.Count

        Dim CurrentCell As Range
        Dim NewCheckbox As CheckBox

        Set CurrentCell = [CheckboxAnchor].Cells(i)
        Set NewCheckbox = Me.CheckBoxes.Add(0, 0, 0, 0)
        With CurrentCell
            NewCheckbox.Width = .Width
            NewCheckbox.Height = .Height
            NewCheckbox.Top = .Top
            NewCheckbox.Left = .Left
        End With

        NewCheckbox.Name = "Check" & i
    Next

End Sub

This code is fairly straightforward. Every worksheet contains a collections object that holds all the CheckBox controls that appear on the sheet. Be careful, however; the collection is not immediately available through IntelliSense. So you need to trust that it is there, even if IntelliSense doesn’t show it. When the check boxes are already created, sometimes it’s easier to refer to them using the Shapes collection as you did earlier in the chapter.

The Checkbox collections object has an Add method. The parameters for this method are left, top, width, and height. Given this, you might be wondering why I would supply this argument with zeros and then adjust the checkbox’s dimensions thereafter. However, in my experience, sometimes changing the width and height after setting the CheckBox control’s coordinates will slightly change its position. Therefore, your best bet is to set the dimensions first and then set the coordinates.

Now, let’s talk about how to provide information about the page you’re on.

Components That Provide Information

This section will describe how to develop components in the spreadsheet-based wizard that provide the user with information. This includes highlighting the steps you’re on, describing the page you’re looking at, and including page-specific instructions to the user. Figure 17-20 highlights these components.

Using Custom Formats to Highlight the Current Step

This section will cover how you can use custom formats (as you did in the first examples in this chapter) to help you highlight which step is currently in view. Figure 17-21 shows an excerpt of the formula. This is essentially the same formula for all the possible steps cells in Column A.

Let’s break down this formula. Recall that -- is simply the shorthand operation to change a text string or Boolean expression into a number. Because every step starts with a given number (e.g. 1. Introduction, 2. Survey, etc), you can read in that number. Above, read in that number by looking at the first character of each step. Left(B4, 1) will return a 1; Left(B5, 1) will return a 2 and so forth. You use the shorthand value operation to turn it into a number.

Once you know the number, you can simply use a Boolean conditional to compare it to the current page you’re on. In Figure 17-19, --LEFT(B4,1)=Helper,CurrentPageIndex would return a FALSE. This is because you are on the second page, and cell A4 refers to the first page. Cell A5 refers to the second page, so it will return a TRUE. The final -- at the end converts the TRUE and FALSE values back to zeros and ones.

To create the dot effects above, you follow a similar custom formula described in the beginning. To all of them, I’ve applied this simple custom format syntax: [Color15][=0]·;[Color9][=1]·.

Using INDEX to Provide Step-Specific Information

This section will cover the finishing touches to your wizard. On the top of every view, I’ve placed the same formula throughout. You can see this formula in Figure 17-22.

In Figure 17-23, you can see that Wizard.StepRange points to the list of steps on the side.

Because Wizard.CurrentPageIndex will always refer to the current step in view, you can simply place this formula at the top of each wizard. This will ensure you always show the correct heading. In addition, you can simply change the title of the step in Wizard.StepRange and the change will be reflected automatically in its corresponding view.

The instructions follows a similar path. There’s an Instructions Table on the Helper tab that includes instructions for each step. The Instructions Table holds particular instructions for each page in the wizard. Take a look at the instructions formula used in Figure 17-24.

Again, you use the current page index to help you pull relevant information for each step.

The Last Word

In this chapter, I talked about building spreadsheets that can capture user input. Spreadsheet-based wizards are particularly useful. You may not have thought that a spreadsheet was a good place to take user input. Conventional wisdom suggests that you should use ActiveX components. However, compared to UserForm-based wizards, spreadsheet-based wizards are easier to build, design, and modify.

In the next chapter, I’ll talk about how to store input from these wizards.