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.
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.
All I had to do was work out how Chris had achieved it.
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.
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.
The second requirement was to recreate the Calculation section
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 item||Type||idMso Reference||Size|
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.
Finally, I added the Calc Buddy button, selecting my own image to use for the icon.
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.