This post may contain affiliate links. Please read my disclosure for more info:

Reverse engineering a standard Excel Ribbon

Reverse engineering a standard Excel Ribbon Thumb

Reverse engineering a standard Excel Ribbon

In July 2017, I released the Calc Buddy Add-in.  It’s a tool which monitors Excel’s calculation mode and shows a warning whenever it is different to the user’s preferred setting.  As I was getting the Add-in ready for release, I decided I wanted the button to be within the Calculation section of the Formula Ribbon.  It seemed crazy to put one button on a new Ribbon tab if that is the only Add-in a user has.

Calc Buddy Added to Formula Ribbon
The final result – Calc Buddy in the Calculation section

There was just one problem with my plan . . . I didn’t know how to add a button to an existing Ribbon.  I knew how to create new Ribbon tabs, but adding to an existing one was a mystery.

Thankfully, I knew somebody else had faced the same issue.  Chris Newman (www.thespreadsheetguru.com) had created an Add-in to insert the Center Across Selection button onto the Alignment section of the Home Ribbon.

Reverse Engineer Standard Ribbon - Center Across Selection
The Center Across Selection button added to the Alignment tab

All I had to do was work out how Chris had achieved it.

If you’ve not yet downloaded your version of the Calc Buddy Add-in, then click here to find out more.

 

Reverse engineering the Center Across Selection Button

From re-reading Chris’s post, there was one key thing I learned – the process is not to add a button to the existing section of the Ribbon, but to hide the existing section, then recreate all of it with the new button added.

Using Andy Pope’s RibbonX Visual Designer Add-in, I opened the Center Across Selection Add-in to look at the Ribbon.  This gave me all the information I needed.

The remainder of this post uses the RibbonX Visual Designer Add-in.  I recommend you read my post on building your own Add-in, as this will give you more information on how to use this tool.

 

Creating the Calc Buddy Menu

The first requirement was to hide the existing Calculation section of the Formula Ribbon.  This was a simple matter of setting the visibility of the Calculation group to false.

Reverse engineer Ribbon - Existing to visible false

The second requirement was to recreate the Calculation section

Reverse engineer Ribbon - Existing ribbon
Calculation section of the Formula tab

This was a bit of guesswork initially.  Eventually I worked out what type of item each element was, then applied the relevant idMso reference and size to it.

Ribbon itemTypeidMso ReferenceSize
CalculationGroupn/an/a
Calculation OptionsMenuCalculationOptionsMenuLarge
Calculate NowButtonCalculateNowNormal
Calculate SheetButtonCalculateSheetNormal

It was not necessary to recreate the drop-down menu for the Calculation Options button.  The drop-down was created automatically by selecting the CalculationOptionsMenu idMso.

 

Reverse Engineer - Calc Buddy Add-in

Finally, I added the Calc Buddy button, selecting my own image to use for the icon.

Reverse engineer Ribbon - Calc Buddy button added

That’s it.  Calc Buddy was now part of the Calculation section of the Formula Ribbon.

Obviously, I have to give thanks to Andy Pope and Chris Newman, as without them I would have no clue what-so-ever.

Save

Leave a Reply

Your email address will not be published. Required fields are marked *