Forum Discussion

keldsor's avatar
keldsor
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's avatar
    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.

HansVogelaar's avatar
HansVogelaar
MVP
Nov 06, 2022

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
  • keldsor's avatar
    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's avatar
      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.

      • keldsor's avatar
        keldsor
        Brass Contributor
        Nov 06, 2022
        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 !

Resources