Forum Discussion
Dustion: How to turn OFF/ON Ribbon, formular bar etc ect ??
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.
The DoCmd lines are Access VBA, not Excel VBA. Try this:
Private Sub Workbook_Close(Cancel As Boolean)
Application.DisplayGridlines = True
Application.DisplayFormulaBar = True
ActiveWindow.DisplayHeadings = True
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
End Sub
Private Sub Workbook_Open()
markerObjecter
ActiveWindow.DisplayGridlines = False
Application.DisplayFormulaBar = False
ActiveWindow.DisplayHeadings = False
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",FALSE)"
nextPNR = ThisWorkbook.Sheets("NR-Ark").Range("A1")
rejectOpenWord = ThisWorkbook.Sheets("NR-Ark").Range("A5")
End Sub
It's too much though - and I think the
Application.ExecuteExcel4Macroconfuses 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 ?
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.
- The gridlines works OK - it was the "Formula Bar" that would NOT turn ON again, but moving the setting to the WorkBook_BeforeClose cures the problem.
I wont modify any CELLS in this specific WorkBook because it's only build for manipulating SHAPES, so IF my VBA change of SETTINGS is making the woorkbook DIRTY (=has to be saved) then it wood be better to just SAVE without asking. A user woold be conmfused if it asks for SAVING when he has made no changes !You can remove the lines that set DisplayGridlines and DisplayHeadings. If you save the workbook once when gridlines and headings are hidden, it will remain that way.
Does the marketObjecter macro change anything? If not, your problem should be solved. Since DisplayFormulaBar is an application setting, it does not affect the "dirty" state of the workbook.
Private Sub Workbook_Open() markerObjecter Application.DisplayFormulaBar = False nextPNR = ThisWorkbook.Sheets("NR-Ark").Range("A1") End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.DisplayFormulaBar = True End Sub