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

excel

7 Replies

  • Jan_Wijninckx's avatar
    Jan_Wijninckx
    Brass Contributor
    Mar 26, 2024

    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!

    • dodger1's avatar
      dodger1
      Copper Contributor
      Nov 01, 2024

      Jan_Wijninckx 

      So sorry not to have responed earlier to you suggestion.

      Just been too busy with "things" to explore your idea.

      Many thanks

      Roger.

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

       

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Nov 22, 2022

    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. 

Resources