Search

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 back to 'normalø again for working in other ordinairy workbooks.

I therefore build this code in the WorkBook:

Option Explicit
Option Base 1
Private Sub Workbook_Close(Cancel As Boolean)
   Application.DisplayGridlines = True
   Application.DisplayFormulaBar = True
   ActiveWindow.DisplayHeadings = True
'   Application.CommandBars.ExecuteMso "ShowRibbon"
'    DoCmd.ShowToolbar "Ribbon", acToolbarYes
End Sub
Private Sub Workbook_Open()
   markerObjecter
   ' Application.DisplayGridlines = False
    ActiveWindow.DisplayGridlines = False
    Application.DisplayFormulaBar = False
    ActiveWindow.DisplayHeadings = False
    'Application.CommandBars.ExecuteMso "HideRibbon"
    'DoCmd.ShowToolbar "Ribbon", acToolbarNo
    nextPNR = ThisWorkbook.Sheets("NR-Ark").Range("A1")
   ' rejectOpenWord = ThisWorkbook.Sheets("NR-Ark").Range("A5")
End Sub

but I doesn't work, so I commented out the problematic lines (the rejactOpenWord is for other use som just forget that line)

What do I do wrong here and how can I fix it ?

@keldsor

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

@Hans Vogelaar

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 ?

@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.
						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 !
						
@keldsor 
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
						
@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.