Get or set the Ribbon visibility

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