Turn off Gridlines won't stick in new window

[Dman333]

Hi all,

I have a workbook where I don't want the gridlines in the background. So, I go to Options - Advanced and turn them off for each sheet in the display options for this worksheet section. When I open the file they're off. That's great.

But, most people use two screens. If I open a new window none of the above settings stick. If I then change them and save it with both workbooks open - when it opens the next time both windows have the gridlines off - that's great.

However, if you do the above (change both windows and save it with both windows open), and then close one window and save the file again (with only one window open) - when I open the file and then open a new window, the new window has the gridlines showing again. There's no way to control how people will save a file after you send it on and I want this to have the gridlines off so it looks cleaner with the cells being used having boarders, shading, etc.. to highlight cells and rows to users.

Is there any way in Vba to force the gridlines to be off for certain sheets or even all if need be) so when a user goes from 1 window to 2 or 3 the worksheet settings for gridlines off sticks? I'm not an expert in Vba, so hopefully someone on here has a solution.

Thanks in advance for any time or effort with this.
Cheers,
Dman333

 

[Jaafar Tribak]

The Gridlines setting is not a Workbook level setting. It is a Window level one.

Try this code in the ThisWorkbook Module:

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    Wn.DisplayGridlines = False
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    ActiveWindow.DisplayGridlines = False
End Sub

 

[Dman333]

Hi Jafar - Thanks a ton for this and your time. It works. But this is strange to me because there is a setting in Advanced Options to turn off the gridlines for a sheet and it works until you open a new window. I guess I just don't understand why that setting isn't applied to the new window for the same workbook and sheet.

The only reason I mention that is because while your method works, and I'll use it if there isn't another solution, there's a quick flicker of the gridlines going off every time you select the sheet while the code executes. This only happens in a second window after selecting new window. The original window doesn't do that.

Anyone have any ideas how to get rid of that or if there's another way to get this done?

Thanks again for your time and effort.
Best,
Dman333

 

[Jaafar Tribak]

Yes. I could see the very brieve flicker. The DisplayGridlines Property reverts back to the default TRUE for any newly added Window ! Even defining a custom sheet view doesn't prevent it.

Furthermore, DisplayGridlines being a Property of the Window Object makes turning off the grid on each worksheet very clumsy as the worksheet must be selected first. In my opinion, this is an ugly flaw in the excel object model design. One would expect the Worksheet Object to have a DisplayGridlines Property.

Anyways, here is a hacky workaround for turning off the grids of all the worksheets, including those in any added new window. It should hopefully fix the flicker issue.

In the ThisWorkbook Module:

Option Explicit

Private Sub Workbook_Open()
    Call TurnOffGrilines(Opening:=True)
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    Call TurnOffGrilines
End Sub

Private Sub TurnOffGrilines(Optional ByVal Opening As Boolean)
    Dim oActiveSheet As Worksheet, oSh As Worksheet
    On Error GoTo errHandler:
    Set oActiveSheet = ActiveSheet
    With Application
        .EnableEvents = False:   .ScreenUpdating = False
        Windows(1&).Visible = Not Opening
        For Each oSh In Me.Worksheets
            oSh.Activate
            .ActiveWindow.DisplayGridlines = False
        Next oSh
        oActiveSheet.Activate
errHandler:
        Opening = True
        Windows(1&).Visible = Opening
        .EnableEvents = True:  .ScreenUpdating = True
    End With
End Sub