PDA

View Full Version : Ribbon Interface Menu builder



MWE
03-13-2016, 11:30 AM
Back in the days of ol2003, John Wallenbach's Power Programming book had a nice little procedure to build a drop down menu dynamically based on data in a spreadsheet tab. I modified his code and created a drop down menu generator (MenuMgmt) that worked in any MSOffice tool (data is still pulled into from an external xl spreadsheet/tab) and have used it continuously for 10+ years in both my applications and those I have developed for others.

With the arrival of Office2007 and the Ribbon/Fluent Interface, the approach still worked but the menu ended up in the Developers tab. After years of playing around with multiple versions of Office, I still hate the ribbon interface and much prefer the old drop down menu approach. Wallenbach's Power Programming for Excel 2007 summarized the dynamic menu situation with (something like) "it sucks". A few more tweaks to MenuMgmt added "popup" menus that worked a bit better in 2007+ applications but it was really a band aid.

A while ago I asked if any knew of a VBA procedure or set of procedures that could create or delete dynamically generated ribbon "tabs". Feedback was not encouraging.

I am now getting requests to rebuild some of my applications using the ribbon interface. Before I sit down and build a menu generator, I will ask again if anyone has or knows about a VB/VBA based ribbon tab creation procedure.

Thanks

Paul_Hossler
03-13-2016, 12:16 PM
I did this awhile ago (based on other people 's ideas and code) as an example of making menus on the ribbon based on WS cells

It doesn't do anything for real, and the 4 drop down all run the same set of macros since this was just a concept

Look over and see if it works for you

MWE
03-14-2016, 07:07 AM
Paul: thanks, you have probably saved me many hours of screwing around and have gotten me thinking about several ways to accomplish what I want. Since the old menu generator works fine in xl2007+ (it just puts the drop down in the Developer tab which is not available to average users), perhaps the best short term approach is to


create a new tab when the application opens
place the old style drop down in that tab
delete the new tab when the application closes


One of the aspects of the ribbon interface that I do not like is the apparent clutter and space absorption on the screen. If you have a giant screen (which is increasingly popular for desktops) then it probably does not matter; but with small screen laptops, you want to manage the space carefully and keep "hidden" what the user does not need at the moment. I am apparently in the minority but I just do not understand why MS (and other s/w developers) pour effort into features that no one really wants and ultimately would like to disable. I recognize their business model but it seems much better for everyone to focus on fixing the problems that never seem to get fixed. Enough of my whining; thanks again. I will get back to you as I fine tune my approach.

JonPeltier
03-14-2016, 08:49 AM
Don't the menus appear on the 'Add-Ins' tab?

Maybe you should learn to create a true custom ribbon tab. It will make your applications look more professional. It's not terribly hard, but requires a different set of skills. You could start with this series on Microsoft's web site: Customizing the 2007 Office Fluent Ribbon for Developers (https://msdn.microsoft.com/en-us/library/aa338202(v=office.12).aspx), and continue with this book: RibbonX: Customizing the Office 2007 Ribbon (http://amzn.to/1LmrBXg).

Paul_Hossler
03-14-2016, 11:23 AM
It's really not that difficult to use the ribbon interface.

As you say, if you just throw buttons onto it, it will 'overflow'

It does require some planning as to which controls are most effective for a task, which to 'emphasize' , and which can be a little harder to get to

The other thing to consider is using context menus, like [Format] when you select a picture

For example, this is the tab for my personal add in. Different controls, sizes, options, etc. Still fits on a laptop


15626

I had done a very complicated ribbon that had toggle buttons to show or hide different groups, since the user didn't need to see every thing all the time

Bob Phillips
03-14-2016, 03:14 PM
The big problem with dynamic ribbons is that you still have to build the XML, define all of your controls and assign ids. You can then use callbacks for all properties and build them in code. This is how I do it, and I have a menu to define all the values, captions, images, action procedures, etc.

But to be honest, if I were you I would use Andy Pope's wonderful ribbon designer http://www.andypope.info/vba/ribboneditor.htm