Excel turn OFF/ON - Ribbon, row/column headers and the 'gridlines' on the sheet

KeldSor 351 Reputation points
2022-11-01T14:20:16.9+00:00

I have ONE SPECIFIC Excel Workbook where the user ONLY have to generate and manipulate some shapes - no formulas and calculation.

I want to let them have as much space on the screen as possible and therefore I have made this code in the OPEN sub: 

    Private Sub Workbook_Open()  
        If ActiveSheet.Shapes.Count > 0 Then Application.CommandBars.ExecuteMso "ObjectsSelect"  
'       Application.DisplayGridlines = False        ' <<<<<<< sometimes this raises an error:     438  Object doesn't support this property or method          
        Application.DisplayFormulaBar = False  
        ActiveWindow.DisplayHeadings = False  
'      Application.CommandBars.ExecuteMso "HideRibbon"     '  <<<<<  Wont work at all !  
'      DoCmd.ShowToolbar "Ribbon", acToolbarNo                   '  <<<<<  Wont work at all !  
    End Sub

Unfortunately these CHANGE of Excel-setting 'lives further' when the user closes this specific WorkBook and maybe wants to open another 'normal' Excel workbook.
I therefore tried to insert this code into the CLOSE sub: 

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

Again some of the code wont work AND what's even worse - a dialog asking if the changes should be saved ... there is NO changes to the workbook 'only' the the Excel-settings !

On the other hand IF THE USER made some changes to the WorkBook - he shall have the possibillity to save them.

What is the 'Best Practice' to manage such a working method ?

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,641 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. KeldSor 351 Reputation points
    2022-11-03T12:34:58.967+00:00

    No one have any ideas

    1) why the code wont work
    2) on how to avoid the SAVE-question

    ?

    0 comments
  2. KeldSor 351 Reputation points
    2022-11-09T08:32:52.343+00:00

    I've found this code works best for me: 

    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_BeforeClose(Cancel As Boolean)  
   Application.DisplayFormulaBar = True  
   ThisWorkbook.Save  
End Sub  
  
Private Sub Workbook_Close(Cancel As Boolean)  
    ActiveWindow.DisplayGridlines = True  
    ActiveWindow.DisplayHeadings = True  
'    Application.CommandBars.ExecuteMso "ShowRibbon"  
'    Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"  
End Sub

    And that "Quick Access Toolbar" simply STINKS - and the guidelines out there is worth nothing at all - outdated or ...

    What is going on ... is it "under construction" or what ... ?

    Mine is divided into two - one in the upper boarder og the screen and one under the ribbon - and the text for the buttons in the part under the ribbon just disappeared - all I found about bringing the text back again have checkbox I DON'T have on my screen - and I should be fully updated !

    0 comments