505.369.1650 [email protected]

If you’ve ever wished you could keep the Office , 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 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 .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

Download Code:

basMinimizeRibbon.zip

Making Microsoft Access Work

No matter what your MS Access needs are, Carl can provide live help to make sure you get the solution you need.