How to Build a Custom Ribbon in Excel
Using XML and VBA
--
Microsoft’s ribbon interface has been with us since the launch of Office 2007. Although met with mixed reaction, over time many acknowledged it was a viable replacement for the ageing bland menus we had become accustomed to.
Microsoft developed the ribbon to increase user awareness of what was nested within each Office product. It was said that users would frequently request new features, oblivious to the fact that the very things they wanted to accomplish were hidden away in amongst a plethora of menu options.
For the most part, the ribbon has solved this issue due to its more aesthetically pleasing design and how it splits up the different sections.
In this article I’m going to focus specifically on the Excel ribbon. However, given the architecture is the same across the board, you can apply the same principles to the other Microsoft products as well.
The wrong way to build a system
Up until a few months ago, the way I would have built a navigation or interactive system in Excel was completely different. I’d have used the in-built Form Controls, which contains objects like buttons, drop-downs and checkboxes—and dumped them at the top of each worksheet. This got the job done, but there were a few disadvantages of this approach:
- each set of navigation buttons had to be repeated for each worksheet
- worksheet real estate was reduced due to the space the systems took up
- scrolling vertically or horizontally meant the navigation buttons went out of view (Freeze Panes was not the solution)
Here’s an example:
Roughly a quarter of the vertical space is occupied by the navigation and interactive systems. This may not matter for people like myself, who look at spreadsheets on a 32" 4K monitor, but for those viewing it on 13" laptops with substandard 1280 x 800 displays — the experience is very different.
