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

[dodger1]

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.

Roger.

(from way back when I learnt Excel Ver: 4&5 - Macro sheets and then VBA)

[#2]

@dodger1 

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

Home (ribboncommander.com)

It is a paid add-in that allows you to control the Ribbon programmatically. 

[#3]

Hmmm.... that's not very encouraging! Even if you are correct.
Is there such a thing as an Office Object Model? If so, how do you access it?

[#4]

@dodger1 

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 

Ribbon Recipes: Beginner's Guide to Creating Excel Menus (Beyond Excel) eBook : Hatmaker, Craig: Ama...

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.

[#5]

@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)"

[#6]

@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.

[#7]

@dodger1 
I had exactly the same issue. ChatGPT couldn't solve it either, but gave me plenty of pointers. So that this doesn't go to waste, here a 3 VBA routines that do what the buttons do:

Sub RibbonShowFull()
    If Application.CommandBars("Ribbon").Visible Then
        Debug.Print "Ribbon is visible, check by how much"
        
        With Application.CommandBars("Ribbon").Controls(1)
            If .Height > 40 And .Height < 155 Then
                Debug.Print "Shows tabs functions only, toggle"
                CommandBars.ExecuteMso "MinimizeRibbon"
            End If
        End With
    Else
        Debug.Print "Is not showing, switch Ribbon on"
        Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"", True)"
        
        With Application.CommandBars("Ribbon").Controls(1)
            If .Height < 155 Then
                Debug.Print "Shows tabs functions only, toggle"
                CommandBars.ExecuteMso "MinimizeRibbon"
            End If
        End With
    End If
End Sub

Sub RibbonShowTabsOnly()
    If Application.CommandBars("Ribbon").Visible Then
        Debug.Print "Ribbon is in view, check by how much"
        
        With Application.CommandBars("Ribbon").Controls(1)
            If .Height > 155 Then
                Debug.Print "Shows full tabs, toggle"
                CommandBars.ExecuteMso "MinimizeRibbon"
            End If
        End With
    Else
        Debug.Print "Ribbon is hidden, switch on"
        Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"", True)"
        
        With Application.CommandBars("Ribbon").Controls(1)
            If .Height > 155 Then
                Debug.Print "Shows full tabs, toggle"
                CommandBars.ExecuteMso "MinimizeRibbon"
            End If
        End With
    End If
End Sub

Sub RibbonHideFromView()
    If Application.CommandBars.GetVisibleMso("TabHome") Then
        Debug.Print "Ribbon is visible, switch off"
        Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"", False)"
    End If
End Sub

Hope that helps!