If you've ever wished you could keep the Office Ribbon, but automatically minimize it to conserve space, this VBA function is for you. The same technique works for Microsoft Access, Microsoft Word, etc.
The function includes two pieces of logic. The first piece checks to see whether or not the ribbon is currently minimized by examining its current height. Once that it determined, the second piece determines whether or not to toggle the visibility of the Ribbon. If it needs to toggle, it sends a Ctrl-F1 using the SendKeys statement. In the SendKeys statement, it uses a Wait argument of True to force the code to halt until the keys have been processed. It may not work otherwise.
You do not need to pass an argument to the function if you want to minimize the Ribbon. You may use the function in the opposite manner (maximize the Ribbon if it is minimized) by passing the function an argument of False.
To use the function in MS Access to conserve space for a form when it opens, be sure to call the function before opening the form. You can do that with a macro, where the first action is to call the function and the next action is to open the form. Calling the function in the Open or Load event of the form is too late; the height of the form has already been determined before those events run.
Code:
Public Function MinimizeRibbon(Optional MakeMin = True)
' This function minimizes or maximizes the ribbon, depending upon the value
' of MakeMin. True minimizes the ribbon and False maximizes the ribbon.
' The default value for MakeMin is True.
' MinimizeRibbon() Version 1.0.0
' Copyright © 2009 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact support@extramiledata.com.
' Use (at your own risk) and modify freely as long as proper credit is given.
On Error GoTo Err_MinimizeRibbon
Dim blnIsMin As Boolean
' The Ribbon has a height of 147 when it is maximized.
' If it is maximized, set the blnIsMin value to False.
If Application.CommandBars.Item("Ribbon").Height = 147 Then
blnIsMin = False
Else
blnIsMin = True
End If
' If the current state is the same as MakeMin, then do
' nothing. Otherwise, toggle the ribbon by using Ctrl-F1.
If MakeMin = blnIsMin Then
' Do nothing.
Else
' Send the keystrokes and wait until they are processed.
SendKeys "^{F1}", True
End If
MinimizeRibbon = True
Exit_MinimizeRibbon:
On Error Resume Next
Exit Function
Err_MinimizeRibbon:
MsgBox Err.Number & " " & Err.Description, vbCritical, "MinimizeRibbon"
MinimizeRibbon = False
Resume Exit_MinimizeRibbon
End Function
Download Code: basMinimizeRibbon.zip