How to display tabs and ribbons when closing Excel?

Question

I've got a macro which auto hides ribbons when this excel file opens however, when I close the file I would like these to reappear so when using other files this means I won't have to format them each time.

I've written the below code for when excel opens/closes. But cannot get the ribbons to reappear without manually clicking.

Private Sub Workbook_Open()

Application.CommandBars.ExecuteMso "HideRibbon"
ActiveWindow.DisplayGridlines = False

ActiveWindow.DisplayHeadings = False

Application.DisplayFormulaBar = False

Application.DisplayFullScreen = True
End Sub


Sub Auto_close()
Application.CommandBars.ExecuteMso "hideRibbon"
Application.CommandBars.ExecuteMso "hideRibbon"

ActiveWindow.DisplayGridlines = True

ActiveWindow.DisplayHeadings = True

Application.DisplayFormulaBar = True
End Sub

Try adding this line **Application.CommandBars("Standard").Visible = True** in your close module. — Imran Malek, Oct 31 '18 at 09:06
@ImranMalek do i remove the Application.CommandBars.ExecuteMso "hideRibbon" lines? — PGD15, Oct 31 '18 at 09:09
@ImranMalek this doesn't work still get the white bar :'( when opening a new file — PGD15, Oct 31 '18 at 09:12

score 5 · Accepted Answer · answered Oct 31 '18 at 10:03

5

This displays the ribbon:

Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"", True)"

This hides the ribbon:

Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"", False)"

answered Oct 31 '18 at 10:03

Vityata

42,633
8
55
100

Imran Malek · Answer 2 · 2018-10-31T09:27:25.863

1

Try this

Private Sub Workbook_Open()
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayHeadings = False
Application.DisplayFormulaBar = False
Application.DisplayFullScreen = True
End Sub

Sub test()
Application.DisplayFullScreen = False
ActiveWindow.DisplayGridlines = True
ActiveWindow.DisplayHeadings = True
Application.DisplayFormulaBar = True
Application.CommandBars("Standard").Visible = True
End Sub

edited Oct 31 '18 at 09:27

answered Oct 31 '18 at 09:12

Imran Malek

1,709
2
13
14

Doesn't work for me i've added a picture to main question showing what my ribbion looks like in a new file and I cannot get vba to revert this back :( – PGD15 Oct 31 '18 at 09:15
Remove Application.CommandBars.ExecuteMso "hideRibbon" this line from open as well ! – Imran Malek Oct 31 '18 at 09:17
Yeah done that still don't work, but open opening the file i want it to be hidden to stop the users from editing the file (most don't know how to turn it off manually so I need a macro to turn it off when i close the file – PGD15 Oct 31 '18 at 09:26
See the code in my answer if you have it like that then save the file and manually unhide the menubar and close and reopen your excel, now if you run the code again it will work. – Imran Malek Oct 31 '18 at 09:29
Im trying to avoid the manual aspect, as I will be giving this forms to others to use and they do not know how to manual do this. This will keep the latest view if I do what you've written but I would like the default view to return when closing. – PGD15 Oct 31 '18 at 09:32
You only have to do this once manually as you had hidden the ribbon with Application.CommandBars.ExecuteMso "hideRibbon" . Once you do it manually and execute the code in the answer again it shall work. – Imran Malek Oct 31 '18 at 09:35
i've tried to open this, then open another file after closing it and it goes back to the hidden view, only works if i manual do it each time i've copied the code exactly as you've got it – PGD15 Oct 31 '18 at 09:36

How to display tabs and ribbons when closing Excel?

2 Answers2