Hide Ribbon/formula bar just in one workbook [SOLVED]

[Cammandk]

I want to hide the ribbon / formula bar just in one excel workbook. I have used the following code in THISWORKBOOK in VBA.

It does hide them but if I open up another workbook it does the same to that workbook and excel itself seems to default to this.

Any help would be appreciated.

David


Private Sub Workbook_Open()

Application.ScreenUpdating = False

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

Application.DisplayFormulaBar = False

Sheets("MainMenu").Select

Application.ScreenUpdating = True


End Sub

 

[P0lar]

Can you add the reverse of the above code into a private sub workbook_beforeclose()macro to switch everything back to excels normal visibility?

 

[sgmpatnaik]

@Cammandk


Hi


add the below code in the workbook module

[pre]

Private Sub Workbook_Deactivate()
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
Application.DisplayStatusBar = True

End Sub

Private Sub Workbook_Activate()
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
Application.DisplayStatusBar = True
Sheets("MainMenu").Select
End Sub

[/pre]

Hope it will solve your problem other wise please inform us


Thanks


SP

 

[Cammandk]

Works great

thanks


SOLVED

 

[jeffreyweir]

Only problem with this is that I seem to recall it wipes the undo stack, meaning users can no longer copy info from another workbook and paste it into this one.

 

[sgmpatnaik]

@jeffreyweir


hi


you can change the code for the paste option as

[pre]

Private Sub Workbook_Activate()
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
Application.DisplayStatusBar = True
Sheets("Sheet1").Select
Application.DisplayPasteOptions = True

End Sub

[/pre]

Thanks


SP

 

[nidhin]

@Cammandk


Hi

I am getting syntax error if I use the below code. Please advise.

Private Sub Workbook_Deactivate()
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
Application.DisplayStatusBar = True

End Sub

I also used the below code too

Private Sub Workbook_Open()
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = Not Application.DisplayStatusBar
ActiveWindow.DisplayWorkbookTabs = False
End Sub

@Cammandk


Hi


add the below code in the workbook module

[pre]

Private Sub Workbook_Deactivate()
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
Application.DisplayStatusBar = True

End Sub

Private Sub Workbook_Activate()
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
Application.DisplayStatusBar = True
Sheets("MainMenu").Select
End Sub

[/pre]

Hope it will solve your problem other wise please inform us


Thanks


SP

error. Please advise


add the below code in the workbook module

[pre]

Private Sub Workbook_Deactivate()
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
Application.DisplayStatusBar = True

End Sub

Private Sub Workbook_Activate()
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
Application.DisplayStatusBar = True
Sheets("MainMenu").Select
End Sub

[/pre]

Hope it will solve your problem other wise please inform us


Thanks


SP