Hide ribbon/formula bar for this workbook only

Hello!

I'm trying to create some vba code that will always make the ribbon and formula bar hidden from the user for this workbook only.

I have put the code in the workbook activate/deactivate events in the ThisWorkbook module but 1. I'm getting a runtime error 'Object variable or With block variable not set' for my ribbon line (even though this line works fine as it's own sub in a different worksheet module). 2. Even if I take this line out to focus on the formula bar line (which does work), nothing happens when I switch between workbooks. The formula bar remains hidden when I open another workbook etc.)

Here is the code I am using;

PHP Code: 

Private Sub Workbook_Activate()
If CommandBars("Ribbon").Height < 0 Then CommandBars.ExecuteMso "MinimizeRibbon"
Application.DisplayStatusBar = True
End Sub 


PHP Code: 

Private Sub Workbook_Deactivate()
If CommandBars("Ribbon").Height < 0 Then CommandBars.ExecuteMso "MinimizeRibbon"
Application.DisplayStatusBar = True

End Sub 


Any ideas?

Thanks!

maybe this?

Sub Workbook_Open()
CommandBars.ExecuteMso "HideRibbon"
End Sub

HTH

Originally Posted by LFKim2018

CommandBars.ExecuteMso "HideRibbon"
HTH

For some reason I get a 'invalid procedure call or argument' error with this one.'

Originally Posted by LFKim2018

Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"

And this one runs but doesn't actually do anything? The ribbon stays as it is. Maybe because I'm running Excel for Mac?

actually I had experienced same problem as you. I had consulted other forum for this and no definite procedure can remedy it.
Pls look for the "pin" icon at the extreme right side of the ribbon - click it before running your program or workbook.

also maybe you would want to try the following snippet:

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

include this in private sub workbook_open

sincerely hoping this would help

LFKim's solution to hide the ribbon (see below) works in a workbook macro.

But be careful it only work when screenupdating is true.


Private Sub Workbook_Open()

Application.ScreenUpdating = True

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

End Sub

I think this is much better. Add this to ThisWorkbook

Private Sub Workbook_Open()
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
End Sub

'** it is advisable to revert to the default > unhide the ribbon

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
End Sub

this time I hope this helps