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

score 14 · Answer 1 · answered Sep 26 '14 at 22:41

14

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

answered Sep 26 '14 at 22:41

Dave Stuart

547
8
22

1

Add Application.CommandBars... to prevent runtime error 91 – Noam Brand Jul 24 '21 at 14:32
This code causes run time error number 1004. – Noam Brand Jul 25 '21 at 16:26
Noam, I have used this code in MS Access since version 2010 with no issue. Are you maybe trying this in Excel? – Dave Stuart Jul 26 '21 at 00:40
Yes, using Excel. – Noam Brand Jul 26 '21 at 08:52

score 7 · Answer 2 · answered Aug 14 '13 at 20:04

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

`If accVer > 13 Then` - accVer holds a string here – user253751 Feb 20 '15 at 00:42 — user253751, Feb 20 '15 at 00:42
FYI `SysCmd(acSysCmdAccessVer)` = `Application.Version` – Brad May 16 '18 at 18:35 — Brad, May 16 '18 at 18:35

Elias · Accepted Answer · 2013-08-14T19:58:51.590

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()

Perfect! I detect Access version and RibbonState, then use the apporpriate ExecuteMSO or sendKeys command. Note "Case 15" required to detect MSAccess 2013. — Mark Pelletier, Aug 14 '13 at 19:53

Danage · Answer 4 · 2016-09-16T04:03:58.633

1

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

edited Sep 16 '16 at 04:03

answered Sep 16 '16 at 03:54

Danage

11
4

score 0 · Answer 5 · edited Jun 01 '20 at 06:21

0

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

edited Jun 01 '20 at 06:21

David Buck

3,752
35
31
35

answered Jun 01 '20 at 00:26

David

1
1

Thanks for fixing the copy-paste error. Not sure how my sub/end ended up outside the code. :) – David Jun 03 '20 at 04:14

mms · Answer 6 · 2023-05-17T08:26:54.593

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

MSAccess - Minimize the Toolbar Ribbon OnLoad()?

6 Answers6

Linked