Ken is an accomplished Excel developer. In his early Excel days, when he had to switch back and forth frequently between two distant worksheets in a large workbook, he used a 'quick-and-dirty' trick: he moved the two sheet tabs side-by-side.

Soon he realized that a much better solution could be to create a Table of Contents for every workbook using hyperlinks in an index sheet, named 'ToC'.

Navigating to any sheet took just one click. But he also needed a way to quickly get back to the ToC sheet. The obvious solution would be to put a hyperlink back to the ToC in cell A1 of each individual worksheet. The problems with that were:

•     Some sheets may already have data in cell A1.
  

•     He didn't not want his 'Back to ToC' hyperlink to print when the sheets were printed.

So Ken, came-up with a clever new solution: He created a text box hyperlink set to not print and copied it to every sheet listed in the table of contents of every workbook he used. That's a lot of work you may think, but it is worth the effort, right ?

[Bill a business analyst working in the finance department of a global company]

Bill: No Ken. It's a chore to maintain all these hyperlinks. Plus:

• You may not be allowed to create a table of contents in workbooks that you haven't authored or are not allowed to modify. 
  
  
• You cannot create an hyperlink to chart sheets directly, since there aren't any cells to link to. You would have to link to an intermediate worksheet and and then activate the chart sheet using a worksheet event.
  
  

You also seem to forget that Excel provides some basic navigation functionality, so you don't have to add VBA code to your workbook. Just right-click the sheet navigation icons (bottom / left).

The ideal sheet navigation tool should be dynamic. Have a look at a free Excel Addin offered with the Ribbon Commander framework. 

Buttons with a flag image are shown in a custom Ribbon tab for any sheet named after a country using the ISO 3166-1 naming standard. All other sheets are ignored. 

The Back / Next buttons navigate only to country sheets. Again, any other sheets will be ignored.

The drop-down button (Active Sheet group) opens a gallery with a list of all sheets in the active workbook. An icon with the sheet tab colour is shown next to the label, when the sheet name doesn't match a country look-up table.

The Columns options sets the number of columns shown in the gallery.

If the Events checkbox is set, the flag navigation Ribbon tab will be interactive, that is it will react to any user sheet tab selection.

As sheet activations may interfere with VBA code, it is recommended that Flag Navigation Addin events are disabled (uncheck Events) before running a macro. (Note: the status of EnableEvents application property is not modified)

---

This case study is presented in an effort to provide Excel practitioners with a way to apply conceptual material to real-world business problems across several sectors. We hope that you find the content engaging, dynamic and helpful in dealing with complex, realistic development problems in Microsoft Excel. While every effort has been made to provide an expert commentary from both academic and practitioner perspectives, the solution(s) presented may not be exhaustive. Please feel free to contribute your suggestion(s). All names and scenarios are fictional.

---

comments powered by Disqus

---

Home  »   Ribbon  »   How to build a sheet navigation Ribbon tab using flag images