top of page
  • John

Get or set the Ribbon visibility

Updated: Jul 29, 2022

The examples here all work with Excel, Word and PowerPoint, they do not work with Outlook.


Set the Ribbon visibility


These methods allow you to control the visibility of the Ribbon. They are the equivalent of the user clicking on the small 'Ribbon Display Options' button in the Excel / Word / PowerPoint window title bar and so the user has the option to reset them when your code finishes running.


To toggle between 'Auto-hide' being on or off (QAT also hidden):



To toggle between 'Show Tabs' and 'Show Tabs and Commands' … will error if the Ribbon is currently in the 'Auto-hide' state (QAT still shown)



And in Word, you also have this, which does the same as "MinimizeRibbon":



Also in Excel, you have these options to control the visibility of the Ribbon. There is no user-equivalent of these methods - if you leave the Ribbon hidden with this method the user can only make it visible again by closing and re-starting Excel ... I tend to avoid these methods but include them here for completeness.


Also, these methods also do not play nicely with Get the Ribbon visibility ... the value returned by that function ignores the fact that the Ribbon is hidden using this method.


To hide the Ribbon (and the QAT):



To make the Ribbon (and the QAT) visible:



Get the Ribbon visibility


This function allows you to get the visibility state of the Ribbon. It relies on the visible height of the Ribbon ... there is always a chance that Microsoft might change the Ribbon height in a future version of Office, but as of Office 2019 / Office 365, it works!


In the declaration section of your module, add an Enum:



Then in the same module add this function:



Example usage:



Limitations: Windows only; Excel, Word and PowerPoint 2007+ only


1 comment

1 Comment


J. Woolley
J. Woolley
Aug 14, 2022

Thank you for inspiring My Excel Toolbox's ToggleRibbon macro, which supports Undo (Ctrl+Z). Here is an abbreviated version:

Sub ToggleRibbon()

Static bNext As Boolean

Const myName As String = "ToggleRibbon"

ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"", " & CStr(bNext) & ")"

bNext = (Not bNext)

Application.OnUndo myName, (ThisWorkbook.Name + "!" + myName)

End Sub


Like
bottom of page