In my first post about Sheet Navigation I showed a basic approach. Here I will show a more practical and realistic solution to navigate between sheets. The example is based on a VSTO workbook however it can be applied to both native Excel solutions as well as managed COM/VSTO add-ins.
When it comes to customize the Ribbon UI with VBA and with VB.NET the major difference is the callbacks signatures. Because I constantly forget which signatures to be used depending on which language I use I check with the following article: Customizing the 2007 Office Fluent Ribbon for Developers (Part 3 of 3).
Anyway, in this example I use the dropDown control but the drawback with it is that it does not explicitly give us the name of the selected item, only its ID and index number. Therefore we need to resolve it in the code.
Although we should strive to create fully dynamic applications, they come with the price of having to write code for all possible scenarios. In real-world applications we therefore tend to create semifixed applications where some parts of the solutions are fixed and some parts are dynamic. In the example here we have a fixed list of sheets whose names will not change during runtime and we therefore do not need to invalidate the dropDown control during runtime. On the other hand, we will not hard-code the sheets names in order to ease the maintenance.
The following screen shot shows the whole VSTO project:
As we can see we have four worksheets and one chart. One of the worksheets, “Hidden”, is hidden which we also need to resolve in the code.
The following screen shot lists the Ribbon UI XML solution:
The next screen shot shows the relevant code in the ThisWorkbook module:
We grab the collection of sheets in the workbook in a sheet collection variable which we use in the callbacks signatures as the following screen shot shows:
It should be noted that I use Option Strict for the example solution, i.e. it forces us to cast. When running it we can navigate between the sheets rather smooth as the following screen shot lists:
The example shows that we can rather easily create robust solutions to navigate between the sheets in a workbook. Compared with the basic approach the above solution gives the end users a better option as it shows the names of the sheets.
In the coming blogpost I will demonstrate how we can implement the Ribbon UI in Windows Forms.