Skip to Content

When we ended last time, we had a mockup of a ribbon in MS Excel, which we later planned to set up to control a simple, dashboard. 

Pic9.png

So let’s get started on enabling the ribbon.  If you are in the Ribbon Designer tab and double click on one of the ribbon buttons, you’ll be whisked away to the source code for handling the events.  Since our project here is a c# project, we have our event code in c#.  In the last installment, I’d mentioned that you should rename your buttons to something more descriptive than toggleButton1 or toggleButton5.  In our case here, the toggle button for Australia is called toggleButtonAU.  You’ll notice methods called <togglebuttonname>_Click().  These are the OnClick event handlers for your toggle buttons.  In the case of toggleButtonAU, the OnClick event handler method is called toggleButtonAU_Click().  Later on in this blog post, we’ll write the code to handle when the button is checked and when it is unchecked. 

Helper Methods

Before we can get to work on our event handlers however, we’ll need three helper methods.  These methods will prevent us from rewriting the same code over and over for every toggle button’s event handler method.  The methods will be as follows:

setFilter() – This method will set the filter on our data source and post a message to the UI using SAPAddMessage, telling the user what we’ve done with the crosstab.  We’re using the Analysis Office SAPAddMessage mechanism here, because a modal dialog would force the user to click it away and we just want a passive status message.  setFilter() will take three parameters; the country key, the message that we intend to display and lastly a Boolean flag indicating whether we want to show or suppress the message.  Normally, we won’t be calling this method directly and the wrapping method will always display the message, but we want to keep this option for later on.

Since this is the first place where we write c# code that interacts with Analysis, I would mention here that we are actually calling the VBA API of Analysis via Application.Run().  This is a general VSTO feature, which allows you to call subroutines written in VBA from VSTO.  The VBA command itself just returns the call to the .Net stack.  By only exposing the VBA version of the API, we simplify maintenance and reduce the number of potential points of failure in Analysis Office.

private void setFilter(string countryID, string countryMessage, bool supressMessage)

{

          object o = Globals.ThisWorkbook.Application.Run(“SAPSetFilter”, “DS_1”, “0D_COUNTRY”, countryID, “KEY”, Type.Missing, Type.Missing,

            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

          if (supressMessage == false)

          {

                    o = Globals.ThisWorkbook.Application.Run(“SAPAddMessage”, countryMessage, “INFORMATION”, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                      Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                      Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                      Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

          }

}

toggleCountry() – This is just a wrapper for setFilter that builds the display message and then calls setFilter, with the latter set to always display the message.  This will be the method that all of our OnClick event handlers will be calling.

private void toggleCountry(string countryID, string countryName)

{

          string aoMessage = “Now filtering to” + countryName + ” only”;

          this.setFilter(countryID, aoMessage, false);

}

untoggleEveryone() – This does what it says.  It sets all the toggle buttons in the ribbon to the unselected state.  By first calling this method, and then toggling the clicked ribbon to the selected state, we can get a radio button like behavior among our country flags.  Oh and notice that we’re calling it with the suppressMessage parameter.  This gives us the option to suppress the message when switching between two countries, but to display it when we are untoggling the currently selected button to turn all filtering off.

private void untoggleEveryone(bool supressMessage)

{

          toggleButtonAU.Checked = false;

          toggleButtonFR.Checked = false;

          toggleButtonDE.Checked = false;

          toggleButtonUS.Checked = false;

          toggleButtonUK.Checked = false;

          toggleButtonAU.SuperTip = “Click to filter to Australia only”;

          toggleButtonFR.SuperTip = “Click to filter to France only”;

          toggleButtonDE.SuperTip = “Click to filter to Germany only”;

          toggleButtonUS.SuperTip = “Click to filter to USA only”;

          toggleButtonUK.SuperTip = “Click to filter to Great Britain only”;

          this.setFilter(“”, “Now showing all countries”, supressMessage);

}

On Click Event Handler

Now we can finally return to toggleButtonAU_Click () and flesh it out. 

  1. The first thing we are going to do is determine whether or not we are already “checked”.  If the button is already “checked” when the user clicks on it, then unset it and all others by executing untoggleEveryone().
  2. Otherwise, call toggleCountry() and pass the country code and country name to it.
  3. Lastly, flag the button as checked and as a last bit of polish, change the supertip to inform the user that clicking on it again will remove the filter.

private void toggleButtonAU_Click(object sender, RibbonControlEventArgs e)

{

          if (toggleButtonAU.Checked == false)

          {

                    untoggleEveryone(false);

          }

          else

          {

                    untoggleEveryone(true);

                    this.toggleCountry(“AU”, “Australia”);

                    toggleButtonUK.Checked = true;

                    toggleButtonUK.SuperTip = “Click to remove filter.”;

          }

}

That’s it!  Rinse and repeat for the OnClick event handler for the other countries and execute it (in debug mode) and clicking on the green arrow up in the Visual Studio menu bar.  When you are happy with it, build an installer using the build menu.  That is out of the scope of this blog post, but hopefully there was enough here to get you going on your way to building Excel plugins and interact with Analysis.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Martin Kreitlein

    Hello David,

    I just read the blog post once and I’m wondering if in the first source code box everything is corret?

    What’s the purpose of the dozens of “…Type.Missing,…” statements?

    Thanks, Martin

    (0) 
    1. David Stocker Post author

      Hi Martin,

      You are passing your call through the VBA interface.  VBA allows optional parameters.  Some other languages, such as Python also allow optional paremeters.  But when calling those subroutines from C#, you are forced to pass something for all of those optional paremeters and the Type.Missing is a way not to actually pass anything, while still conforming to the “letter of the law”.

      If you are repeatedly calling a particular subroutine, you can create a c# wrapper method so that you’ve got something cleaner to call.

      Cheers,

      Dave

      (0) 

Leave a Reply