"Sticky" ActiveWindow properties

[Cheer]

Here's an interesting "feature": Some window properties are more
"sticky" than others, at least in Excel 2000 (9.0).

To see what I mean, open a new workbook and make sure it has at least
two worksheets, "Sheet1" and "Sheet2." On Sheet1, pull up
Tools/Options and in the "Window options" part of the form, turn off
gridlines for that sheet (deselect the checkbox). OK out of the dialog
box and observe Sheet1's gridlines to be hidden. Go to Sheet2 - its
gridlines are still there. Back to Sheet1, gridlines still gone.

While on Sheet1, open up Tools/Options again, reselect gridlines, and
this time turn off the "Vertical scroll bar." OK out of the dialog box
and verify that Sheet1's vertical scroll bar is indeed missing. Now go
to Sheet2 - its vertical scroll bar is gone too!

This latter "sticky" behavior appears to exist only for the following
properties of the ActiveWindow:

.DisplayHorizontalScrollBar
.DisplayVerticalScrollBar
.DisplayWorkbookTabs

I could not find this documented in the Excel help files. This makes
trying to capture a workbook's "state" difficult because you cannot
cycle through, activating each sheet in turn, and storing its
properties so you can restore those properties later. As soon as you
get to a sheet whose DisplayVerticalScrollBar property is set to
false, every sheet after that will have that property set to false
too.

 

[Tom Ogilvy]

As soon as you

get to a sheet whose DisplayVerticalScrollBar property is set to
false, every sheet after that will have that property set to false
too.

That wouldn't happen. As you say, when it is off, it is off for all sheets.

So you should be safe saving all properties for each sheet and restoring
same - the end result being what the user had before.

 

[NickHK]

Tom,
This seems a little strange to me, as all those properties belong to
Excel.Window, but their behaviour is different.
I seem to remember that in previous versions these properties did not all
belong to .Window, but I may be mistaken.

NickHK

 

[Tom Ogilvy]

I believe you are mistaken. For example, displaying a grid has always been
specific to the sheet (verified at least back to xl95). Of course in Excel 4
and prior, there was no such thing as multiple sheets in a
workbook/worksheet. So a window setting would obviously be global in that
case. I suspect some of the apparent "inconsistency" is due to the
evolution from single sheet workbooks to multisheet workbooks and the BIFF
file format.

 

[Cheer]

Thank you, Tom and Nick, for your replies.

I proclaim there is a hitherto unknown addition to the Excel Object
Model called the "Frankenstein."

For the .window uhh frankenstein, the .caption property refers to the
workbook.

..DisplayGridlines refers to the sheet (dialog and work).

..DisplayVerticalScrollBar, however, again refers to the workbook.

No wonder I don't know what I'm doing. It's not a bird, it's not a
plane, it's a frankenstein.

What I'd hoped to do is bring up a blank worksheet with gridlines,
headers, scrollbars, and sheet tabs turned off whenever a userform is
displayed. I thought I could do that by setting those properties (as
false) for a sheet, hiding it, and then making that sheet visible
again when needed.

I think I now see that I only need capture the values for
DisplayVerticalScrollBar, DisplayHorizontalScrollBar, and
DisplaySheetTabs before displaying my blank sheet, and then restore
those after that sheet has again been hidden.

My only remaining question is, when dealing with .window properties,
should I carry a stake or a silver cross?