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 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 ?

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.

6 Replies

  • 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.

Resources