Confusion about how the Window object fits into the Excel object model

I'm a bit confused about how the Window object fits into the Excel object
model and am hoping somebody can set me straight. Here's my deal ...

My application wants to create a new worksheet and have its gridlines and
headings be set the same as another worksheet in a different workbook. I
understand that the DisplayGridlines and DisplayHeadings properties are part
of the Window object and not the worksheet object. In fact if you search
through all of the properties of a workbook and its constituent parts you
can't find anything that controls these two apparent aspects of the
worksheet.

Yet, if you go to Tools | Options and check or uncheck these properties to
change the appearance of the currently active worksheet, save the workbook
(which presumably doesn't save the active window) and then reopen the
workbook, the gridlines and headings are restored to their state at the time
of the save.

So presumably, these properties of the window are being saved with the
workbook? But where? What am I missing?

It bugs me to have this gap in my mental model of how Excel is strung
together. Again I hope somebody will set me straight on how this hangs
together.

TIA,

josh

I would imagine the attributes are stored as part of the worksheet
information since they are specific to the worksheet. However, they are not
directly accessible through the worksheet object. When a worksheet is
activated, I assume the window gets the setting from the worksheet
attributes. A workbook stores a calculation setting, but it is set
through the application object as another example of something similar.

--
Regards,
Tom Ogilvy

"Josh Sale" <jsale@tril dot cod> wrote in message
news:%[email protected]...
> I'm a bit confused about how the Window object fits into the Excel object
> model and am hoping somebody can set me straight. Here's my deal ...
>
> My application wants to create a new worksheet and have its gridlines and
> headings be set the same as another worksheet in a different workbook. I
> understand that the DisplayGridlines and DisplayHeadings properties are
part
> of the Window object and not the worksheet object. In fact if you search
> through all of the properties of a workbook and its constituent parts you
> can't find anything that controls these two apparent aspects of the
> worksheet.
>
> Yet, if you go to Tools | Options and check or uncheck these properties to
> change the appearance of the currently active worksheet, save the workbook
> (which presumably doesn't save the active window) and then reopen the
> workbook, the gridlines and headings are restored to their state at the
time
> of the save.
>
> So presumably, these properties of the window are being saved with the
> workbook? But where? What am I missing?
>
> It bugs me to have this gap in my mental model of how Excel is strung
> together. Again I hope somebody will set me straight on how this hangs
> together.
>
> TIA,
>
> josh
>
>

Josh,

I beleive the items you are looking for are properties of the Activewindow .
Therefore Application.Activewindow.Displaygridlines should work.

"Josh Sale" wrote:

> I'm a bit confused about how the Window object fits into the Excel object
> model and am hoping somebody can set me straight. Here's my deal ...
>
> My application wants to create a new worksheet and have its gridlines and
> headings be set the same as another worksheet in a different workbook. I
> understand that the DisplayGridlines and DisplayHeadings properties are part
> of the Window object and not the worksheet object. In fact if you search
> through all of the properties of a workbook and its constituent parts you
> can't find anything that controls these two apparent aspects of the
> worksheet.
>
> Yet, if you go to Tools | Options and check or uncheck these properties to
> change the appearance of the currently active worksheet, save the workbook
> (which presumably doesn't save the active window) and then reopen the
> workbook, the gridlines and headings are restored to their state at the time
> of the save.
>
> So presumably, these properties of the window are being saved with the
> workbook? But where? What am I missing?
>
> It bugs me to have this gap in my mental model of how Excel is strung
> together. Again I hope somebody will set me straight on how this hangs
> together.
>
> TIA,
>
> josh
>
>
>

Thanks Tom. I thought I was going crazy.

Any idea why the Excel designers would have chosen to make these worksheet
properties private and only expose them via the Window object? Seems odd to
me.

I only started looking at Excel's object model starting with XL97. I wonder
if this is some kind of artifact from earlier versions of Excel where these
appearance settings might have cut across worksheets and workbooks (i.e.,
perhaps in XL1 they really were specific to the window and not what's
displayed in it)?

Again thanks for the response.

josh




"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
>I would imagine the attributes are stored as part of the worksheet
> information since they are specific to the worksheet. However, they are
> not
> directly accessible through the worksheet object. When a worksheet is
> activated, I assume the window gets the setting from the worksheet
> attributes. A workbook stores a calculation setting, but it is set
> through the application object as another example of something similar.
>
> --
> Regards,
> Tom Ogilvy
>
> "Josh Sale" <jsale@tril dot cod> wrote in message
> news:%[email protected]...
>> I'm a bit confused about how the Window object fits into the Excel object
>> model and am hoping somebody can set me straight. Here's my deal ...
>>
>> My application wants to create a new worksheet and have its gridlines and
>> headings be set the same as another worksheet in a different workbook. I
>> understand that the DisplayGridlines and DisplayHeadings properties are
> part
>> of the Window object and not the worksheet object. In fact if you search
>> through all of the properties of a workbook and its constituent parts you
>> can't find anything that controls these two apparent aspects of the
>> worksheet.
>>
>> Yet, if you go to Tools | Options and check or uncheck these properties
>> to
>> change the appearance of the currently active worksheet, save the
>> workbook
>> (which presumably doesn't save the active window) and then reopen the
>> workbook, the gridlines and headings are restored to their state at the
> time
>> of the save.
>>
>> So presumably, these properties of the window are being saved with the
>> workbook? But where? What am I missing?
>>
>> It bugs me to have this gap in my mental model of how Excel is strung
>> together. Again I hope somebody will set me straight on how this hangs
>> together.
>>
>> TIA,
>>
>> josh
>>
>>
>
>

Actually Bob, it doesn't work. You need something like:

sourcesheet.activate
dg = activewindow.displaygridlines
dh = activewindow.displayheadings
targetsheet.activate
activewindow.displaygridlines = dg
activewindow.displayheadings = dh

j



"Bob H" <[email protected]> wrote in message
news:[email protected]...
> Josh,
>
> I beleive the items you are looking for are properties of the
> Activewindow .
> Therefore Application.Activewindow.Displaygridlines should work.
>
> "Josh Sale" wrote:
>
>> I'm a bit confused about how the Window object fits into the Excel object
>> model and am hoping somebody can set me straight. Here's my deal ...
>>
>> My application wants to create a new worksheet and have its gridlines and
>> headings be set the same as another worksheet in a different workbook. I
>> understand that the DisplayGridlines and DisplayHeadings properties are
>> part
>> of the Window object and not the worksheet object. In fact if you search
>> through all of the properties of a workbook and its constituent parts you
>> can't find anything that controls these two apparent aspects of the
>> worksheet.
>>
>> Yet, if you go to Tools | Options and check or uncheck these properties
>> to
>> change the appearance of the currently active worksheet, save the
>> workbook
>> (which presumably doesn't save the active window) and then reopen the
>> workbook, the gridlines and headings are restored to their state at the
>> time
>> of the save.
>>
>> So presumably, these properties of the window are being saved with the
>> workbook? But where? What am I missing?
>>
>> It bugs me to have this gap in my mental model of how Excel is strung
>> together. Again I hope somebody will set me straight on how this hangs
>> together.
>>
>> TIA,
>>
>> josh
>>
>>
>>

There is a windows collection for the Application object and the workbook
object (not worksheet). Take a look at Window object in help.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Josh Sale" <jsale@tril dot cod> wrote in message
news:%[email protected]...
> I'm a bit confused about how the Window object fits into the Excel object
> model and am hoping somebody can set me straight. Here's my deal ...
>
> My application wants to create a new worksheet and have its gridlines and
> headings be set the same as another worksheet in a different workbook. I
> understand that the DisplayGridlines and DisplayHeadings properties are
part
> of the Window object and not the worksheet object. In fact if you search
> through all of the properties of a workbook and its constituent parts you
> can't find anything that controls these two apparent aspects of the
> worksheet.
>
> Yet, if you go to Tools | Options and check or uncheck these properties to
> change the appearance of the currently active worksheet, save the workbook
> (which presumably doesn't save the active window) and then reopen the
> workbook, the gridlines and headings are restored to their state at the
time
> of the save.
>
> So presumably, these properties of the window are being saved with the
> workbook? But where? What am I missing?
>
> It bugs me to have this gap in my mental model of how Excel is strung
> together. Again I hope somebody will set me straight on how this hangs
> together.
>
> TIA,
>
> josh
>
>

Thanks, Josh. Sorry I totally misunderstood your question on first reading
it. I 'll be more careful in the future.

"Josh Sale" wrote:

> Actually Bob, it doesn't work. You need something like:
>
> sourcesheet.activate
> dg = activewindow.displaygridlines
> dh = activewindow.displayheadings
> targetsheet.activate
> activewindow.displaygridlines = dg
> activewindow.displayheadings = dh
>
> j
>
>
>
> "Bob H" <[email protected]> wrote in message
> news:[email protected]...
> > Josh,
> >
> > I beleive the items you are looking for are properties of the
> > Activewindow .
> > Therefore Application.Activewindow.Displaygridlines should work.
> >
> > "Josh Sale" wrote:
> >
> >> I'm a bit confused about how the Window object fits into the Excel object
> >> model and am hoping somebody can set me straight. Here's my deal ...
> >>
> >> My application wants to create a new worksheet and have its gridlines and
> >> headings be set the same as another worksheet in a different workbook. I
> >> understand that the DisplayGridlines and DisplayHeadings properties are
> >> part
> >> of the Window object and not the worksheet object. In fact if you search
> >> through all of the properties of a workbook and its constituent parts you
> >> can't find anything that controls these two apparent aspects of the
> >> worksheet.
> >>
> >> Yet, if you go to Tools | Options and check or uncheck these properties
> >> to
> >> change the appearance of the currently active worksheet, save the
> >> workbook
> >> (which presumably doesn't save the active window) and then reopen the
> >> workbook, the gridlines and headings are restored to their state at the
> >> time
> >> of the save.
> >>
> >> So presumably, these properties of the window are being saved with the
> >> workbook? But where? What am I missing?
> >>
> >> It bugs me to have this gap in my mental model of how Excel is strung
> >> together. Again I hope somebody will set me straight on how this hangs
> >> together.
> >>
> >> TIA,
> >>
> >> josh
> >>
> >>
> >>
>
>
>

Hi Josh,

This might give an idea of what's going on:

Sub Test()
Dim i As Long
Dim wb As Workbook
Dim ws As Worksheet
Dim w As Window

Set wb = Workbooks.Add
Set w = wb.NewWindow

wb.Windows(1).DisplayGridlines = False

wb.Windows(2).Activate
wb.Worksheets(2).Activate
wb.Windows(1).DisplayGridlines = False

'ThisWorkbook.Activate
wb.Windows(2).Activate
For i = 1 To 2
wb.Windows(i).Activate
For Each ws In wb.Worksheets
ws.Activate
Debug.Print wb.ActiveSheet.Name, _
"Window-" & i, wb.Windows(1).DisplayGridlines
Next
Debug.Print
Next

End Sub

You should find you can apply different settings to the same sheet in
different windows.
Note Window(1) is the active window of the WB, regardless of how many
windows it has or if the WB itself is active.

But there's something rather strange about the WB/Windows collection, eg
it's possible to run two different customized palettes in the same WB
(there's a bit of a nack to doing that ! ).

Regards,
Peter T

"Josh Sale" <jsale@tril dot cod> wrote in message
news:#[email protected]...
> I'm a bit confused about how the Window object fits into the Excel object
> model and am hoping somebody can set me straight. Here's my deal ...
>
> My application wants to create a new worksheet and have its gridlines and
> headings be set the same as another worksheet in a different workbook. I
> understand that the DisplayGridlines and DisplayHeadings properties are
part
> of the Window object and not the worksheet object. In fact if you search
> through all of the properties of a workbook and its constituent parts you
> can't find anything that controls these two apparent aspects of the
> worksheet.
>
> Yet, if you go to Tools | Options and check or uncheck these properties to
> change the appearance of the currently active worksheet, save the workbook
> (which presumably doesn't save the active window) and then reopen the
> workbook, the gridlines and headings are restored to their state at the
time
> of the save.
>
> So presumably, these properties of the window are being saved with the
> workbook? But where? What am I missing?
>
> It bugs me to have this gap in my mental model of how Excel is strung
> together. Again I hope somebody will set me straight on how this hangs
> together.
>
> TIA,
>
> josh
>
>

Until Excel version 5, a worksheet, workbook and window were synonymous
since there were only one each (there really was no workbook). So I suspect
it somewhat evolved from that.

--
Regards,
Tom Ogilvy

"Josh Sale" <jsale@tril dot cod> wrote in message
news:[email protected]...
> Thanks Tom. I thought I was going crazy.
>
> Any idea why the Excel designers would have chosen to make these worksheet
> properties private and only expose them via the Window object? Seems odd
to
> me.
>
> I only started looking at Excel's object model starting with XL97. I
wonder
> if this is some kind of artifact from earlier versions of Excel where
these
> appearance settings might have cut across worksheets and workbooks (i.e.,
> perhaps in XL1 they really were specific to the window and not what's
> displayed in it)?
>
> Again thanks for the response.
>
> josh
>
>
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> >I would imagine the attributes are stored as part of the worksheet
> > information since they are specific to the worksheet. However, they are
> > not
> > directly accessible through the worksheet object. When a worksheet is
> > activated, I assume the window gets the setting from the worksheet
> > attributes. A workbook stores a calculation setting, but it is set
> > through the application object as another example of something similar.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Josh Sale" <jsale@tril dot cod> wrote in message
> > news:%[email protected]...
> >> I'm a bit confused about how the Window object fits into the Excel
object
> >> model and am hoping somebody can set me straight. Here's my deal ...
> >>
> >> My application wants to create a new worksheet and have its gridlines
and
> >> headings be set the same as another worksheet in a different workbook.
I
> >> understand that the DisplayGridlines and DisplayHeadings properties are
> > part
> >> of the Window object and not the worksheet object. In fact if you
search
> >> through all of the properties of a workbook and its constituent parts
you
> >> can't find anything that controls these two apparent aspects of the
> >> worksheet.
> >>
> >> Yet, if you go to Tools | Options and check or uncheck these properties
> >> to
> >> change the appearance of the currently active worksheet, save the
> >> workbook
> >> (which presumably doesn't save the active window) and then reopen the
> >> workbook, the gridlines and headings are restored to their state at the
> > time
> >> of the save.
> >>
> >> So presumably, these properties of the window are being saved with the
> >> workbook? But where? What am I missing?
> >>
> >> It bugs me to have this gap in my mental model of how Excel is strung
> >> together. Again I hope somebody will set me straight on how this hangs
> >> together.
> >>
> >> TIA,
> >>
> >> josh
> >>
> >>
> >
> >
>
>

Makes sense. Thanks.

j



"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Until Excel version 5, a worksheet, workbook and window were synonymous
> since there were only one each (there really was no workbook). So I
> suspect
> it somewhat evolved from that.
>
> --
> Regards,
> Tom Ogilvy
>
> "Josh Sale" <jsale@tril dot cod> wrote in message
> news:[email protected]...
>> Thanks Tom. I thought I was going crazy.
>>
>> Any idea why the Excel designers would have chosen to make these
>> worksheet
>> properties private and only expose them via the Window object? Seems odd
> to
>> me.
>>
>> I only started looking at Excel's object model starting with XL97. I
> wonder
>> if this is some kind of artifact from earlier versions of Excel where
> these
>> appearance settings might have cut across worksheets and workbooks (i.e.,
>> perhaps in XL1 they really were specific to the window and not what's
>> displayed in it)?
>>
>> Again thanks for the response.
>>
>> josh
>>
>>
>>
>>
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:[email protected]...
>> >I would imagine the attributes are stored as part of the worksheet
>> > information since they are specific to the worksheet. However, they
>> > are
>> > not
>> > directly accessible through the worksheet object. When a worksheet
>> > is
>> > activated, I assume the window gets the setting from the worksheet
>> > attributes. A workbook stores a calculation setting, but it is set
>> > through the application object as another example of something similar.
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> > "Josh Sale" <jsale@tril dot cod> wrote in message
>> > news:%[email protected]...
>> >> I'm a bit confused about how the Window object fits into the Excel
> object
>> >> model and am hoping somebody can set me straight. Here's my deal ...
>> >>
>> >> My application wants to create a new worksheet and have its gridlines
> and
>> >> headings be set the same as another worksheet in a different workbook.
> I
>> >> understand that the DisplayGridlines and DisplayHeadings properties
>> >> are
>> > part
>> >> of the Window object and not the worksheet object. In fact if you
> search
>> >> through all of the properties of a workbook and its constituent parts
> you
>> >> can't find anything that controls these two apparent aspects of the
>> >> worksheet.
>> >>
>> >> Yet, if you go to Tools | Options and check or uncheck these
>> >> properties
>> >> to
>> >> change the appearance of the currently active worksheet, save the
>> >> workbook
>> >> (which presumably doesn't save the active window) and then reopen the
>> >> workbook, the gridlines and headings are restored to their state at
>> >> the
>> > time
>> >> of the save.
>> >>
>> >> So presumably, these properties of the window are being saved with the
>> >> workbook? But where? What am I missing?
>> >>
>> >> It bugs me to have this gap in my mental model of how Excel is strung
>> >> together. Again I hope somebody will set me straight on how this
>> >> hangs
>> >> together.
>> >>
>> >> TIA,
>> >>
>> >> josh
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Peter thanks for the insight and taking the time to create this bit of code
that so clearly demonstrates the distinction between windows and worksheets.

You've filled a few chinks in my understanding of Excel's object model.

j

Hi Josh,

This might give an idea of what's going on:

Sub Test()
Dim i As Long
Dim wb As Workbook
Dim ws As Worksheet
Dim w As Window

Set wb = Workbooks.Add
Set w = wb.NewWindow

wb.Windows(1).DisplayGridlines = False

wb.Windows(2).Activate
wb.Worksheets(2).Activate
wb.Windows(1).DisplayGridlines = False

'ThisWorkbook.Activate
wb.Windows(2).Activate
For i = 1 To 2
wb.Windows(i).Activate
For Each ws In wb.Worksheets
ws.Activate
Debug.Print wb.ActiveSheet.Name, _
"Window-" & i, wb.Windows(1).DisplayGridlines
Next
Debug.Print
Next

End Sub

You should find you can apply different settings to the same sheet in
different windows.
Note Window(1) is the active window of the WB, regardless of how many
windows it has or if the WB itself is active.

But there's something rather strange about the WB/Windows collection, eg
it's possible to run two different customized palettes in the same WB
(there's a bit of a nack to doing that ! ).

Regards,
Peter T

"Josh Sale" <jsale@tril dot cod> wrote in message
news:#[email protected]...
> I'm a bit confused about how the Window object fits into the Excel object
> model and am hoping somebody can set me straight. Here's my deal ...
>
> My application wants to create a new worksheet and have its gridlines and
> headings be set the same as another worksheet in a different workbook. I
> understand that the DisplayGridlines and DisplayHeadings properties are
part
> of the Window object and not the worksheet object. In fact if you search
> through all of the properties of a workbook and its constituent parts you
> can't find anything that controls these two apparent aspects of the
> worksheet.
>
> Yet, if you go to Tools | Options and check or uncheck these properties to
> change the appearance of the currently active worksheet, save the workbook
> (which presumably doesn't save the active window) and then reopen the
> workbook, the gridlines and headings are restored to their state at the
time
> of the save.
>
> So presumably, these properties of the window are being saved with the
> workbook? But where? What am I missing?
>
> It bugs me to have this gap in my mental model of how Excel is strung
> together. Again I hope somebody will set me straight on how this hangs
> together.
>
> TIA,
>
> josh
>
>