Navigation in Excel with VBA

Using VBA to control navigation elements of a spreadsheet provides a greater level of flexibility. Through proper coding, users will be provided with responsive navigation and proper feedback.

DifficultyIntermediate
Time Est.60-90 Minutes
VBA/MacrosYes
Template Files
Solution Files

Spreadsheet Design Exercise 3

This exercise of the Spreadsheet Design series, continues the focus on Navigation

While the previous solutions for Text Hyperlink and Shape Hyperlink navigation are sufficient for separating your spreadsheet application into different sections, they do not provide the full flexibility that a VBA solution is able to provide. Using VBA is limitless, and although the solution in this post results in a very similar effect to the non-VBA solution, you could potentially further develop this solution tailored exactly to fit the needs of your project.

In addition to its limitless flexibility, implementing navigation through VBA can accomplish a very user friendly goal – having a complex spreadsheet or Excel application exist on one workbook tab. The most effective Excel applications act and feel like desktop software or web-based applications — moving away from the constraints a tab navigation or interface better mimic actual desktop application. For robust applications, it’s important that the user’s interaction with Excel as a program be limited as much as possible. Helping the user avoid the use of workbook tabs is one way to accomplish that.

This approach effectively replaces Excel’s native workbook tabs with a “file tab” visualization at the header of the interface sheet. While the end result is similar to what Excel’s built-in workbook tabs provide, there are a few reasons for implementing this approach:

  1. Navigation will be prominent on worksheet. A user may not be fully aware that navigation tabs are.
  2. As a developer, you will have full control over the look and format of navigation within your Excel application. This control will allow you to provide design elements which inform the user as to where they need to go, and what each area will accomplish.
  3. For more elaborate programs, other subroutines and functions can be triggered by navigation activities. This could potentially eliminate steps a user must go through to update dashboards or modify data.
  4. Worksheet tabs tend to be a mix of interface tabs, forms, and datasets. While hiding tabs can help manage this, it may not always be clear to the user.

While the setup and coding for this approach may be somewhat complex, the idea is quite simple. By clicking on a navigation topic within the worksheet, rows are hidden or made visible to show the content related to the topic. The remainder of this post will show you how to implement this with a basic setup and lean VBA code.

For today’s exercise, use the previously completed Text Link Navigation solution as the basic content template. With a VBA approach, it’s better to have at least some content on the page before navigation setup. The template stripped of hyperlinks can be downloaded at the top of this post.

For the initial pre-VBA setup:

  1. Cut and paste the APPL and MSFT data tables to Sheet1, pasting the tables vertically on the sheet, directly below the charts. Leave 1 empty row between each content section (example is below in Step 1).
  2. Delete Sheet2 and Sheet3.
  3. Insert a new column before column A (moving all contents one column to the right).

Likewise, you could also start with a fresh workbook with your own content. The key is to keep ALL the content on one page, fully vertical.

Step 1: Page setup.

Before opening the VBA editor, you must set up ranges for the spreadsheet content and navigation menu. In our example with 3 content section (Dashboard, AAPL Data, MSFT Data), set up 7 named ranges: 3 ranges on column A that span each content section, 1 range for the entire menu group, and 1 range each for each menu link/button. The ranges are highlighted as follows:

Set up named ranges that correspond with the outlines.
Set up named ranges that correspond with the outlines.

Note that in addition to assigning a named range to the menu bar, you should assign a named range to each menu item. Some good practices to follow are:

  • Ranges should be given a title that makes logical sense, and identifies its purpose. In this example, the ranges identifying content sections start with “View” followed by an underscore and short descriptive word/phrase. The menu ranges start with “Menu” followed by an underscore, and the name of a menu item. The range grouping the menu together is named “Menu_Group”.
    • In this example, the descriptor for both the menu item and content section is the same. E.g. View_Dashboard and Menu_Dashboard. This is a good practice that could lead to cleaner code and easier maintenance, but is not required.
  • When naming the content ranges, the first and last cell of the range should specify–hard coded/typed into the actual cell–the named range with identifiers for start and end. In this example, the “View_Dashboard” range has “View_Dashboard [Start]” in cell A6, and “View_Dashboard [End]” in cell A20. This will act as a helpful reference when adding/removing content, as the range name is never clearly visible.
  • Content ranges must always be in the leftmost column, which can be hidden after setup.

The mechanics of this method work as follows:

  • A Worksheet_SelectionChange event is triggered when a user presses on one of the menu items.
  • A script within the event (1) hides all rows for all the content ranges, then (2) unhides the individually selected content range.

Step 2: Two approaches to navigation with VBA.

Easy approach:

The simple approach provides a static menu that simply hides/unhides the content ranges. This approach is good for a quick implementation, although maintenance may be cumbersome if additional menu items are added. Coding is only required in the worksheet object

In this approach, adding a new menu item would require the addition of a new conditional nest, along with adding a new hide command to each of the already existing conditional nests. Additionally, you will need to make redundant updates should you need to change the name of any ranges.

The elaborate approach allows us to get around these maintenance issues by including the functionality in a single subroutine.

Elaborate approach:

The elaborate approach is better for future maintenance/changes, and also includes a few additional features. In addition to the expected content navigation effects, the menu now provides a “file tab” effect by making the active menu button drop its bottom border and revert to the same background color as the content section.

The tab navigation effect is a good feature to help the user keep track of location.
The tab navigation effect is a good feature to help the user keep track of location.

This approach requires coding to be placed in both the worksheet object and in a coding module. Within the worksheet object, insert the following code:

The worksheet object now only includes the selection change reference with a nested call to subroutine Navigation_Manager. One of the significant benefits in this solution over the simple solution is that when adding a new menu item, you will only need to add a new conditional nest and call to the subroutine. Likewise, any range name changes will not require redundant updates.

Within a standard VBA module, insert the following code:

The subroutine Navigation_Manager hides all content areas, then unhides a single content area based on the value passed through the Selection parameter. The function also contained a second parameter for Menu_Item, which provides an argument for the menu range. However, going back to earlier in the post where a suggestion was made on similar descriptors (View_Dashboard and Menu_Dashboard), you could make this subroutine leaner by only passing one argument (let’s call it, Content), then using “View_” & Content and “Menu_” & Content within the range references.

Screen Captures

Navigation provided with VBA.

Other Notes

In addition to a implementing a navigation system for your Excel application, this approach can be used to walk users through a complex set of steps, such as a monthly reconciliation process or a budget builder. Here’s an example of how this may look.

2016-10-14-3

In this example, the user is on step 5 of a 9 step process. Another feature in this example prevents the user from advancing to future steps until all requirements are set to proceed. The user can return to previously completed steps at any time to change inputs.

Other Notes and Issues

  • Along with some of the drawbacks mentioned in previous posts about workbook navigation, this particular approach brings on a few other concerns. If your Excel application uses any ActiveX Controls or Form Controls, there is a known issue where their height will change to zero when the workbook is saved while they are in hidden ranges. This means that they will seemingly disappear from the worksheet and will need to be manually resized. There are some work-arounds which will be discussed in a future post. Additionally, users will need to active macros to make this navigation feature work.

Leave a Reply