MSAccess - Minimize the Toolbar Ribbon OnLoad()?

Question

I'm looking for a reliable method to minimize the default MSAccess Toolbar Ribbon during the OnLoad() event.

I realize can totally HIDE the toolbar, but that's not exactly what I am looking to do - I just want to minimize the ribbon:

DoCmd.ShowToolbar "Ribbon", acToolbarNo    'Hides the full toolbar
DoCmd.ShowToolbar "Ribbon", acToolbarYes   'Show

I've tried a couple approaches, with mixed success:

In Access 2010 & 2013 (VB7):

CommandBars.ExecuteMso "MinimizeRibbon"

Earlier Versions:

SendKeys "^{F1}", False

Both of these approaches appear to operate as a TOGGLE between sessions. Is there a method to determine the current state and then apply the appropriate code?

I have users with Access: 2007, 2010, 2013

Thanks for any suggestions!

Mark

Barnacle

Answer 1

Check out this answer on MSDN. He shares a few different ways to go about it, including a sample database.

E.G. In Access 2010 you can change the Ribbon state with:

CommandBars.ExecuteMso "MinimizeRibbon"

http://social.msdn.microsoft.com/Forums/office/en-US/2f0d95a8-ed5f-4007-831d-05ef7e7a4263/minimize-the-ribbon-at-access-startup-using-vba

He links within:

http://www.accessribbon.de/en/index.php?FAQ:19

http://www.accessribbon.de/en/index.php?Downloads:15

Based on what access is being used, you could use different functions, perhaps.

Taking this from - http://windowssecrets.com/forums/showthread.php/142262-How-to-find-Access-version-in-code:

Public Function AccessVersionID() As String


   Select Case SysCmd(acSysCmdAccessVer)
     Case 7: AccessVersionID = "95"
     Case 8: AccessVersionID = "97"
     Case 9: AccessVersionID = "2000"
     Case 10: AccessVersionID = "2002"
     Case 11: AccessVersionID = "2003"
     Case 12: AccessVersionID = "2007"
     Case 13: AccessVersionID = "Pirated!"
     Case 14: AccessVersionID = "2010"
     Case 15: AccessVersionID = "2013"
     Case Else: AccessVersionID = "Unknown"
   End Select

 End Function            'AccessVersionID()

Hall

Perfect! I detect Access version and RibbonState, then use the apporpriate ExecuteMSO or sendKeys command. Note "Case 15" required to detect MSAccess 2013. – Barnacle

Answer 2

Access 2010 version and up you should do this in your start-up form. If you just use the ExecuteMso line ONLY it will TOGGLE your Ribbon each time that form opens. To always minimize the Ribbon on Startup then I use the following code.

If CommandBars("ribbon").Height > 100 Then
    CommandBars.ExecuteMso "MinimizeRibbon"
End If

Hope this Helps some who is looking for the answer to this like myself

Dave

Kolodgie

Add Application.CommandBars... to prevent runtime error 91 – Cucumber

This code causes run time error number 1004. – Cucumber

Noam, I have used this code in MS Access since version 2010 with no issue. Are you maybe trying this in Excel? – Kolodgie

Yes, using Excel. – Cucumber

Answer 3

Here's a snippet of my implementaion:

Select Case SysCmd(acSysCmdAccessVer)
    Case 7: accVer = "95"
    Case 8: accVer = "97"
    Case 9: accVer = "2000"
    Case 10: accVer = "2002"
    Case 11: accVer = "2003"
    Case 12: accVer = "2007"
    Case 13: accVer = "Pirated!"
    Case 14: accVer = "2010"
    Case 15: accVer = "2013"
    Case Else: accVer = "Unknown"
End Select

RibbonState = (CommandBars("Ribbon").Controls(1).Height < 100)

Select Case RibbonState
    Case True
        'Do nothing, already minimized
    Case False
        If accVer > 13 Then
            CommandBars.ExecuteMso "MinimizeRibbon"
        Else
            SendKeys "^{F1}", False
        End If
End Select

Barnacle

If accVer > 13 Then - accVer holds a string here – Echolocation

FYI SysCmd(acSysCmdAccessVer) = Application.Version – Stereotaxis

Answer 4

Just moved to Access 2016. My database uses similar code to that provided by Dave Stuart. Looks like minimized ribbon now has height of '102', so have used (e.g.):

If CommandBars("ribbon").Height > 120 Then
  CommandBars.ExecuteMso "MinimizeRibbon"
End If

Guadalcanal

Answer 5

Sub ToggleRibbon(Optional Show)

    If IsMissing(Show) Then
        CommandBars.ExecuteMso "MinimizeRibbon"   'Toggle

    ElseIf Show = True Then
        If CommandBars("ribbon").Height < 100 Then
            CommandBars.ExecuteMso "MinimizeRibbon"
        End If

    ElseIf Show = False Then
        If CommandBars("ribbon").Height > 100 Then
            CommandBars.ExecuteMso "MinimizeRibbon"
        End If

    End If

End Sub

Shame

Thanks for fixing the copy-paste error. Not sure how my sub/end ended up outside the code. :) – Shame

Answer 6

For Access 2007 try this code to minimise the ribbon. The advantage is that you do not need to put in a hard coded value of ribbon height to check whether it is minimised or not.

Dim init_ribbon_height As Integer

Application.Echo False

init_ribbon_height = Application.CommandBars("Ribbon").Height
Sendkeys "^{F1}", False
DoEvents
If Application.CommandBars("Ribbon").Height > init_ribbon_height Then
    Sendkeys "^{F1}", False
    DoEvents
End If

Application.Echo True

Pentateuch