Keep options permanent fora given workbook

[Jean-Luc]

Hi,

I have unchecked "gridlines" and "zero values" (tools / options / view) .

But if I open a second window, these options are reset to default value.
Same problem if I make a copy of a worksheet

Please can you tell me how I could make these options permanent, for a given
workbook.

Thank you and kind regards.

 

[Gary''s Student]

Don't use:

Insert > Worksheet

Once you have set up the existing sheets, keep a spare, formatted, sheet.
If you need a new sheet, just make a copy of the spare.

 

[Jean-Luc]

Hi, Gary"s Student
Thank you for prompt reply ... but sorry :
1. my very first question is about "opening a second window" . Using a kind
of template will not help.
2. below the question, I mentioned "Same problem if I make a copy of a
worksheet"
Please can somebody help ????
Kind regards,
Jean-Luc

 

[Billy Liddel]

Hi Jean-Luc

Post this into The Workbook code Module (Right-click Excel icon left of File
Menu & choose View code)

Private Sub Workbook_NewSheet(ByVal Sh As Object)

With ActiveWindow
.DisplayGridlines = False
.DisplayZeros = False
End With

End Sub


Regards
Peter Atherton

 

[Jean-Luc]

Billy,

Thank you for this useful tip .... but does not work when creating a new
WINDOW (not talking about a new worksheet).
Awfully sorry

Would somebody have a solution ?

thank you and kind regards,

Jean-Luc

 

[Billy Liddel]

Jean-Luc

Are you sure this is what you want? Every sheet in every open workbook to to
have no gridlines or zeros.

I would copy the following macros into your Personal Workbook. Then you can
run it whenever you want.

Sub AllWorkbooks()
Dim Wkb As Workbook
Dim wkbCount As Integer

For Each Wkb In Workbooks
Grids
Next Wkb

End Sub


Sub Grids()
Dim sht As Integer
Dim shtCount As Integer
Dim sShtName As String
Application.ScreenUpdating = False
shtCount = ActiveWorkbook.Sheets.Count
sShtName = ActiveSheet.Name

For sht = 1 To shtCount
Worksheets(sht).Activate
With activewindow
.DisplayGridlines = False
.DisplayZeros = False
End With
Next sht

' Return to the activesheet
Sheets(sShtName).Activate
Application.ScreenUpdating = True

End Sub


You could assign a shortcut key for AllWorkbooks macro.

Hope this suits.

Peter