Nov 22 2022 09:50 AM
Nov 22 2022 09:50 AM
How can you programatically change the visibility of the ribbon in Excel & Word, and add custom features like shorcuts to macros?
Presumably, the 'ribbon' is an Object that has properties and methods, but is not part of, or contained within the 'Application' Object?
I can see how to do this manually by 'Customising the Ribbon' and selecting or de-selecting a feature; but how is this automated in code? Using the macro recorder does not achieve this, as although the ribbon appers to be part of an open worksheet in the active window, Excel can't access the ribbon programatically.
If this is a feature of Office (as it's applicable to several Office programmes), how do you programme Office 'Objects' from within Office applications?
I can't see anyway to access Office and its Objects and their methods and propeties.
(from way back when I learnt Excel Ver: 4&5 - Macro sheets and then VBA)
Nov 22 2022 10:15 AM
I think the answer is "you don't". The Ribbon is not controllable directly through the Excel Object Model but, rather, requires the insertion of XML.
If Ribbon customisation is going to be a major part of your professional life, you might wish to look at
It is a paid add-in that allows you to control the Ribbon programmatically.
Nov 22 2022 03:31 PM
Nov 23 2022 12:10 AM
The Office object models I know of are specific to the application.
A possible source of information on the ribbon is the e-book by @Craig_Hatmaker
There may be a simple way of hiding the ribbon programmatically without getting so deep into the creation of Ribbon controls but it is not something I have experience of.
Nov 23 2022 04:04 AM
You can completely hide the ribbon using the VBA line
and show it again using
Nov 23 2022 05:31 AM
Yes, thanks I was aware of the "ExecuteExcel4Macro" as shown in 'Microsoft Excel Function Reference" for Vers 4 on pg. 398.
NEVER DISPOSE OF THOSE EARLY BOOKS THAT CAME WITH THE SOFTWARE!!!
Use on Macro Sheets ONLY
But, isn't there a VBA equivalent???
Furthermore, it's not the showing or hiding of the toolbar/ribbon I'm interested in; but showing or hiding a member of the ribbon’s collection, even if I have manually create the Custom tab (VBA Proceedures (Custom) and then add the ‘Macros’ sub menu, and its contents.
1St Image shows the settings for the initial state of the ribbon, with the added Custom tab not selected.
2nd image shows the ribbon with the added Custom tab selected, and ‘VBA Procedures’ visible on the ribbon.
3rd image shows the added Custom Tab expanded to show the ‘Macros’ accessible.
It’s the ability to hide/unhide the custom ‘VBA Procedures’ tab within VBA I’m seeking.
The ‘VBA Procedures’ tab needs to be hidden when a workbook is opened, if any of the macros available are not suitable to be used on the open workbook.