Forum Discussion

Brass Contributor

Nov 06, 2022

Solved

Dustion: How to turn OFF/ON Ribbon, formular bar etc ect ??

I have a WorkBook where I want the users to have as mush space on the screen as possible - they shall manipulate different kinds of shapes. When finished working that WorkBook I want to set Excel ba...

Macros and VBA

HansVogelaar
Nov 06, 2022
keldsor

Aargh - I should have seen that. It should be

Private Sub Workbook_BeforeClose(Cancel As Boolean) ActiveWindow.DisplayGridlines = True ...

The event is BeforeClose, not Close, and DisplayGridlines is a window property, not an application property.

The Show Gridlines and Show Headings settings are stored in the workbook. Since you change them in the code, Excel sees the workbook as changed, even if you haven't modified any cells.

keldsor

Brass Contributor

Nov 06, 2022

HansVogelaar

It's too much though - and I think the

Application.ExecuteExcel4Macro

confuses more than it clears up - so I'll drop that.

I now have this code:

Private Sub Workbook_Open()
    markerObjecter
    ActiveWindow.DisplayGridlines = False
    Application.DisplayFormulaBar = False
    ActiveWindow.DisplayHeadings = False
'    Application.CommandBars.ExecuteMso "HideRibbon"
'    Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",FALSE)"
    nextPNR = ThisWorkbook.Sheets("NR-Ark").Range("A1")
End Sub

Private Sub Workbook_Close(Cancel As Boolean)
   ActiveWindow.DisplayGridlines = True
   Application.DisplayFormulaBar = True
   ActiveWindow.DisplayHeadings = True
'   Application.CommandBars.ExecuteMso "ShowRibbon"
'   Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
End Sub

But still there is problem with the Formula bar - it turns OFF but not ON again !

AND

When closing the file it asks for SAVE or NOT - eventhough I did NOT change anything !

Is there a cure for that ?

HansVogelaar

MVP

Nov 06, 2022

keldsor

Aargh - I should have seen that. It should be

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveWindow.DisplayGridlines = True
    ...

The event is BeforeClose, not Close, and DisplayGridlines is a window property, not an application property.

The Show Gridlines and Show Headings settings are stored in the workbook. Since you change them in the code, Excel sees the workbook as changed, even if you haven't modified any cells.