VBA minimize ribbon in Excel

Question

I want to minimize the ribbon in Excel 2013 with VBA. I do not want to toggle the ribbon, and I do not want to hide everything including "File", "Insert", etc. I have tried several different methods, but none satisfy what I want.

This hides everything:

Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"",False)

This toggles:

CommandBars.ExecuteMso "MinimizeRibbon"

This also toggles:

SendKeys "^{F1}"

How can I simply force my ribbon to be minimized?

Why do you want to confuse the user? Let the user decide if the ribbon should be visible or not. — adrianm, Sep 26 '13 at 06:14
Where does it say the user does not get to decide? Click a button, run a macro, decision made. — Kyle Weller, Sep 26 '13 at 06:21
how about `Application.DisplayFullScreen = True` in the `Workbook_Open()` event? — , Sep 26 '13 at 07:14
Thanks @mehow, but that does the same thing as my first example and I don't want to hide everything. — Kyle Weller, Sep 27 '13 at 15:48

score 9 · Answer 1 · answered Aug 10 '14 at 15:11

Measure Ribbon height, toggle it, measure again and if taller, re-toggle. Best also to set Application.Screenupdating = false.

ht1 = Application.CommandBars("Ribbon").Height
SendKeys "^{F1}", False
DoEvents
ht2 = Application.CommandBars("Ribbon").Height
If ht2 > ht1 Then SendKeys "^{F1}", False

And I do hate it when folk question why you want to do what you want. I have a Dictator App and need 100% control over the interaction with Excel.

score 7 · Accepted Answer · answered Sep 26 '13 at 19:31

7

Not sure when you are trying to call this but this will work to minimize the ribbon if its open

If Application.CommandBars("Ribbon").Height >= 150 Then
    SendKeys "^{F1}"
End If

Open Ribbon minimum size seems to be 150 so this will only toggle if it's open

answered Sep 26 '13 at 19:31

engineersmnky

25,495
2
36
52

1

Thanks, this is great, but I notice that my ribbon has height of 147 when open and 61 when closed. So I set my threshold at 100. – Kyle Weller Sep 27 '13 at 15:42
2

sendkeys would fail if launched from a Userform because the worksheet doesn't have the focus, so either use Appactivate before sendkeys, or use the commandbars code – Patrick Lepelletier Oct 27 '15 at 16:04
1

This is great but be aware that Microsoft are always tinkering! With the relatively new touch mode the collapsed/expanded ribbon changes from 101/197 to 104/226 with the QAT below the ribbon and 65/161 to 68/197 with the QAT above the ribbon.. on my PC at least! – Jamie Garroch - MVP Feb 20 '20 at 19:51

score 0 · Answer 3 · edited May 06 '20 at 11:11

Provided no-one else is messing with the settings (and if everyone did what I'm to suggest it would be great), if you retoggle ribbon minimise before close, it will always be there on open so it CAN be minimised. Just make sure to manually hide the ribbon before exiting this one time, then is will always hide on open.

Private Sub Workbook_Open()

Application.DisplayFormulaBar = False
Application.CommandBars.ExecuteMso "MinimizeRibbon"

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.DisplayFormulaBar = True
Application.CommandBars.ExecuteMso "MinimizeRibbon"

End Sub

score -3 · Answer 4 · edited Apr 14 '14 at 13:59

-3

If you want this to be hidden completely as soon as the workbook is opened then add this to the workbook code:

Private Sub Workbook_Open()
    Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
End Sub

edited Apr 14 '14 at 13:59

answered Apr 14 '14 at 13:41

RJSK

1

It seems the OP has already tried this and was not satisfied with the result. – Apr 14 '14 at 14:01

VBA minimize ribbon in Excel

4 Answers4

Linked