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:
(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:
&nbps;
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.
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.
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
Very smart you are Sam. (In my best Yoda voice)
Obvious, the reason is. Avoid it, Microsoft could have.
No need to select for DisplayGridlines or DisplayHeadings.
With 2 workbooks open this activates 1st and removes gridlines and headings from 2nd workbook.
@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
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.WorksheetViewFor Each vw In ActiveWindow.SheetViews
vw.DisplayGridlines = True
Next
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!
@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.
worksheets("sheet3").select:Activewindow.SheetViews("Sheet1").displaygridlines=falseFrans/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 !!
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
objSourceWindow.Activate
ws.Select
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
objTargetWindow.Activate
ws.Select
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
Next
' 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
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?
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
ActiveWindow.DisplayGridlines=false
End If
End Sub