A couple of quirky bugs…

Found a couple of funny bugs today that I thought I’d share.

First, this one:

Weird: Partially italicizing right-aligned text screws with the display of trailing spaces. But it doesn’t do the same to left-aligned text with leading spaces:

And then there’s this one, where you start with a file that you’ve suppressed gridlines on:
…and then you add a New Window…which allows you to look at a different parts of the same file on dual monitors – or even the same monitor if it’s suitably wide, by clicking on this:
New Window2

(Aside: I never rated this feature as being particularly useful until I got a second monitor and then recently rediscovered it. Now I’m starting to think is indispensable, as I no longer have to scroll around half as much as I do when getting to grips with how spreadsheets are laid out, or when putting in new formulas that point from one region of a workbook to a completely different region of the same workbook. I’m sure Dick has been using it for years to avoid rodenting.)

But when you compare the new window (which Excel temporarily renamed SomeFileName:2) to the old (which Excel temporarily renamed SomeFileName:1), there’s a couple of subtle differences:


So it respects my wishes to not show the Formula bar, but ignores my wish to not display gridlines and headings. And if I close the original window – the one called SomeFileName:1 – then those settings I didn’t want from SomeFileName:2 are now in the file SomeFileName. Bummer.

So make sure that’s the one you close when you’re done. Otherwise your file now has gridlines and/or headings when you didn’t want it to.


  1. Adding a window to a workbook has never respected the current window setings as far as I know.

    This is extremely annoying if you decide to close the wrong window and then save your file. You loose ALL gridline, formula bar and heading settings.

    To avoid that, closely watch the titlebar and make sure you DO NOT close the window with :1.

  2. sam says:

    The reason is obvious.

    The DisplayFormulaBar property is implemented in the Application Object
    The DisplayGridlines and DisplayHeadings is implemented in the ActiveWindow Object
    Hence you can see the same sheet one with Gridlines and onewithout in different windows.
    It is also one of the few properties you cant change without selecting/activating the Object

  3. Chris Macro says:

    Very smart you are Sam. (In my best Yoda voice)

  4. Jeff Weir says:

    Obvious, the reason is. Avoid it, Microsoft could have.

  5. Andy Pope says:

    No need to select for DisplayGridlines or DisplayHeadings.
    With 2 workbooks open this activates 1st and removes gridlines and headings from 2nd workbook.

  6. sam says:

    I meant If you are on Sheet1 of a Workbook and You want to remove Gridlines of Sheet3 of the same workbook you need to either Select / Activate Sheet3 to do so.

    Like wise if you have 2 Workbooks open in which Sheet 1 is active in both books, then you cant change the Display gridlines of Sheet 3 unless you Select/Activate Sheet3

  7. Frans Bus says:

    If DisplayGridlines is a true property of the Excel.Window object, then setting ActiveWindow.DisplayGridlines to False should remove the gridlines of all worksheets in the Active window, shouldn’t it? It doesn’t.
    ActiveWindow.DisplayGridlines is an interface to the Worksheetview object that is associated with the Activesheet in the Activewindow. To remove the gridlines of all sheets without selecting/activating:

    Dim vw As Excel.WorksheetView
    For Each vw In ActiveWindow.SheetViews
        vw.DisplayGridlines = True

    The other properties of the WorksheetView object are: DisplayFormulas, -Headings, -Outline and –Zeros.
    Properties like Zoom and Ruler behave the same, and I assume they are also stored in the Worksheetview object. Unfortunately they are not exposed to us. Now THAT is annoying!

  8. Andy Pope says:

    @Sam, I did misunderstand your comment.

    So so sheets within the same workbook. This will remove gridlines from Sheet1 whilst Sheet3 is active, but I see Frans beat me to it. Note Worksheetview was introduced in xl2007.

  9. sam says:

    Frans/Andy – Thanks never heard of the SheetViews – Looks Like I have to re-write a bunch of code where I was looping through sheets to switch off the Gridlines !!

  10. Frans Bus says:

    Often when I want to remove the gridlines I also want to set the Zoom, which is not in the Worksheetview. That’s why I hardly ever use this object.
    Below the code of my CreateNewWindow function which I wrote some time ago. It’s a button in my personal Tools tab, and takes away most if not all of the annoyances mentioned above. Hope this helps.

    Public Function CreateNewWindow()
        Dim objSourceWindow     As Excel.Window
        Dim objTargetWindow     As Excel.Window
        Dim ws                  As Excel.Worksheet
        Dim a(1 To 13)          As Variant
        Application.ScreenUpdating = False
        Application.EnableEvents = False ' make sure no events fire when activating window
        ' get pointers to source and target window
        Set objSourceWindow = ActiveWindow
        ActiveWindow.NewWindow ' new window is now activewindow
        Set objTargetWindow = ActiveWindow
        For Each ws In ActiveWorkbook.Worksheets ' skip charts
            ' get the settings in the source window
            With ActiveWindow
                a(1) = .DisplayFormulas
                a(2) = .DisplayGridlines
                a(3) = .DisplayHeadings
                a(4) = .DisplayOutline
                a(5) = .DisplayZeros
                a(6) = .DisplayHorizontalScrollBar
                a(7) = .DisplayRightToLeft
                a(8) = .DisplayVerticalScrollBar
                a(9) = .DisplayWorkbookTabs
                a(10) = .DisplayRuler ' xlPageLayoutView only
                a(11) = .DisplayWhitespace ' xlPageLayoutView only
                a(12) = .GridlineColorIndex
                a(13) = .Zoom
            End With
            ' modify the settings of the target window
            With ActiveWindow
                .DisplayFormulas = a(1)
                .DisplayGridlines = a(2)
                .DisplayHeadings = a(3)
                .DisplayOutline = a(4)
                .DisplayZeros = a(5)
                .DisplayHorizontalScrollBar = a(6)
                .DisplayRightToLeft = a(7)
                .DisplayVerticalScrollBar = a(8)
                .DisplayWorkbookTabs = a(9)
                .DisplayRuler = a(10)
                .DisplayWhitespace = a(11)
                .GridlineColorIndex = a(12)
                .Zoom = a(13)
            End With
        ' arrange the windows of the active workbook
        objSourceWindow.Activate ' arrange source window left
        ActiveWorkbook.Windows.Arrange ArrangeStyle:=xlTiled
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End Function
  11. Interesting. I knew that the Window object had all the display properties – show gridlines, hedings etc – and that this is a volatile object that does not preserve all its attributes from session to session.

    It appears that the sheetview is a persistent object which allows programmatic access to the subset of window attributes that happen to be persistent across sessions, and that it’s saved at workbook level.

    Does anyone have any more than that?

Posting code or formulas in your comment? Use [cc] tags!

  • [cc_vb]Block of code goes here[/cc_vb]
  • [cci_vb]Inline code goes here[/cci_vb]
  • [cc]Formula goes here[/cc]

Leave a Reply