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?

Answer 1

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.

Answer 2

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