I have a custom app in the form of a workbook with multiple sheets which are reconfigured on the fly by an extensive macro suite. Users have always triggered macros as needed with a single click in my custom toolbar, which is automatically loaded when the workbook is opened. (The toolbar has around 25 customised buttons.) This has worked just fine in every version of Excel for over 15 years, but Excel 2016 drops support for custom toolbars entirely.
As far as I can make out, in Excel 2016 macros can only be triggered by -
(a) selecting from the list in Tools > Macros and then clicking Run OR
(b) associating the macros with worksheet objects (buttons/shapes/textboxes/etc) which are clicked by the user OR
(c) using keyboard shortcuts.
These options do not work for me because:
(a) is far too cumbersome - users sometimes need to click several of my toolbar buttons per minute,
(b) hides the triggering objects from users most of the time: they need to be able trigger any macro instantly, no matter which worksheet they are on and no matter how the active worksheet is scrolled,
(c) users would need to memorise 25+ keyboard shortcuts!
In the absence of a custom toolbar, the only way I can see to make ALL the macros ALWAYS available (no matter what part of the workbook users are looking at) is to
(a) duplicate the trigger objects onto every sheet of the workbook and
(b) place them all in a non-scrolling pane on each worksheet.
But even this doesn't really solve my problem, because I already use small non-scrolling panes to show running totals and to show current workbook status.
Any ideas? (Apart from cursing the Excel development team for their Ribbon obsession.)
BTW, I develop in Excel for Mac, and all my users also run Mac Excel, but this issue is common to Mac and Windows.
Bookmarks