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.

12 thoughts on “A couple of quirky bugs…

  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. 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. No need to select for DisplayGridlines or DisplayHeadings.
    With 2 workbooks open this activates 1st and removes gridlines and headings from 2nd workbook.

  4. @Andy.
    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

  5. 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!

  6. @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.

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

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

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

  10. The solution for me was to add an event macro to “This workbook” (in VBA project of my spreadsheet ) that triggers on window activate.

    Private sub Workbook_WindowActivate(ByVal Wn as Window)

    If ActiveWindow.DisplayGridlines Then


    End If

    End Sub

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.