Forum Discussion

dodger1's avatar
dodger1
Copper Contributor
Nov 22, 2022

MS Ribbon - how to set its visible property to true/false in office programmes?

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 method...
excel
HansVogelaar's avatar
HansVogelaar
MVP
Nov 23, 2022

dodger1 

You can completely hide the ribbon using the VBA line

ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",FALSE)"

and show it again using

ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",TRUE)"
dodger1's avatar
dodger1
Copper Contributor
Nov 23, 2022

HansVogelaar 

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

Syntax is:

SHOW.TOOLBAR(bar_id,visible,dock,x_pos,y_pos,width)

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.

See below:

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.

 

Resources