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 [email protected]. ' 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 greater than 80 when it is maximized. ' If it is maximized, set the blnIsMin value to False. If Application.CommandBars.Item("Ribbon").Height > 80 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
Making Microsoft Access Work. Call (505) 369-1650.
You do realize that you can just double click the bugger and it shrinks up….do you?
goodness sometimes vba code is overused
Yes, I am aware that a single-click will minimize/maximize the Ribbon temporarily and a double-click will fix it in place as minimized or maximized. Sometimes it is enough to train my database users to take advantage of that feature. Other times, the combination of a maximized ribbon and a tall form may resize the form when it opens so that not all of the form is visible to the user. I would prefer that the user see all of the form if possible when it opens, so I use some VBA code to ensure that the ribbon is minimized before the form opens.
Instead of testing for the height of the ribbon you can simply check the “visible” property (because the height of the ribbon is different depending on environment):
If Application.CommandBars.Item(“Ribbon”).visible _= true Then
My understanding is that the Visible property determines whether or not a command bar is visible, not whether or not it is minimized. My code above keeps the ribbon visible, but reduced in size.
Thanks for the code. The height of my ribbon bar is 150 (Vista/Access 2007) so I used >100 as the test (instead of =147) and it works for me – so far. In my case the database is used in a golf club by mostly senior citizens who are not computer literate and definitely frightened of mice. Sometimes the (overuse?) of VBA code is definitely warranted.
I tried the code above. It works the first time, but then it toggles back to maximize because the height is still 147 even though it’s in minimize state. Do you have another work around?
Thank you for the comments above and thanks to John Nessel for his help determining a more generic height condition to use for testing whether or not the ribbon is maximized. The = 147 was changed to > 80. It now works for Excel as well.