VSTO & .NET & Excel

May 5, 2009

Sheet Navigation in The Ribbon UI – Part II

Filed under: .NET & Excel, VSTO & Excel — Dennis M Wallentin @ 11:40 pm

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: 

project-sheetnavigation

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:

ribboncustom-sheetnavigation

The next screen shot shows the relevant code in the ThisWorkbook module:

code-thisworkbook-sheetnavigation

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:

code-ribbonui-sheetnavigation

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:

running-the-sheet-navigation

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.

Kind regards,
Dennis

Advertisements

2 Comments »

  1. Hi Dennis,
    Nice post, I’ve not got my head around call backs and so on yet, but yet agian I will know where to come when I do run into problems!!!

    I’m in the process of writing a worksheet tool in vsto, and handling re-naming of sheets is very tricky (esp. given the interop over head!!) I will blog all about it when I’m done!

    Thanks and good work!

    Ross

    Comment by ross — May 6, 2009 @ 9:53 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: