avoiding select / activate

User avatar
stuck
Panoramic Lounger
Posts: 8128
Joined: 25 Jan 2010, 09:09
Location: retirement

avoiding select / activate

Post by stuck »

Having added a new worksheet to a workbook

Code: Select all

Sheets.Add(After:=Sheets("anExisitingSheet")).Name = "newSheet"
I want to set the zoom to 90% and freeze the top row of that new sheet. If I first select or activate the new worksheet it's easy, all I need is:

Code: Select all

With ActiveWindow
        .Zoom = 90
        .SplitColumn = 0
        .SplitRow = 1
        .FreezePanes = True
End With
but is it possible to do this without first explicitly selecting / activating the new Worksheet?

Thanks,

Ken

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: avoiding select / activate

Post by HansV »

Zoom, SplitColumn etc. are properties of the Window object, not of the Worksheet object.
When you set these properties, they are applied to whichever sheet is the active sheet in that window.
There is no way to apply them to a sheet that is not the active sheet in one of the workbook's windows.
Best wishes,
Hans

User avatar
stuck
Panoramic Lounger
Posts: 8128
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: avoiding select / activate

Post by stuck »

Ah yes, I see now :thankyou: that helps a lot.

Ken

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15499
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: avoiding select / activate

Post by ChrisGreaves »

HansV wrote:
20 Sep 2021, 10:50
Zoom, SplitColumn etc. are properties of the Window object, not of the Worksheet object....
When I glanced at this I nearly spilled my coffee on The Desktop.
I had always thought of stuff inside an Excel workbook as belonging to Excel. I mean, I know that Windows is hovering in the background behaving like the white-coated operators used to behave - mounting tapes, loading decks of cards - but why should Windows govern who/why/what does what to which parts of a workbook?
Then I realized that "the Window object" is Excel's way of talking about what it chooses/has to show to the user.

Then it all made sense.

Which prompts the follow-on question: "Why not of the Worksheet object?"

I haven't thought deeply on this (today is one of this month's Voting Days), but I can't think of a reason why Ken shouldn't be able to code

Code: Select all

With Worksheets("newSheet")
        .Zoom = 90
        .SplitColumn = 0
        .SplitRow = 1
        .FreezePanes = True
End With
The proof of the pudding is that even the User knows that when they set the active window showing the "active sheet" to Zoom=90, it will be at Zoom=90 when they switch it back into view.

We can do that with pages of a Word processing document, as long as we define the zoom area using New Sections and set the zoom factor within a user-defined section.

Logically, as Ken desires, it ought not be necessary to see something in order to change the way it will be seen in the future. This is the rationale behind "peekaboo", the first game that every mother on the planet teaches the baby. "Just because you can't see Mummy, it doesn't mean she's not here". (I reprove all mothers for teaching triple-negatives, but we'll let that pass for now).

[later]Interesting! Zoom is not considered to be a change of a workbook. I fired up Excel, Zoomed "Sheet 1" to 25%, switched to Sheet2 (appears at 100%) then back to Sheet1 (still at 25% as predicted), then quit Excel and was NOT prompted to save the workbook, so a Zoom is not considered to be a change to a workbook. More research needed here :evilgrin:

Cheers
Chris
An expensive day out: Wallet and Grimace

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: avoiding select / activate

Post by StuartR »

One reason for this is because you can have the same Worksheet open in two different windows, with two different zoom levels
You do not have the required permissions to view the files attached to this post.
StuartR


User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15499
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: avoiding select / activate

Post by ChrisGreaves »

StuartR wrote:
20 Sep 2021, 12:10
One reason for this is because you can have the same Worksheet open in two different windows, with two different zoom levels
Stuart, you are an Olympic-sized swimming-pool, based on how quickly you can make me get out of my depth! :grin:

I *think* that you have opened a workbook twice, and set Zoom in one instance and a different zoom in the other instance. If that is so, then yes, clearly, each instance of the worksheet has a different zoom factor, but only in the context of the (two) opened copies of the workbook.
Then comes the question "So when we close the workbooks, which Zoom factor is saved?" and the answer based on experience with data stored in a workbook would be "whichever copy of the workbook was saved later".
But wait (1) Excel is notorious for not allowing two workbooks with the same name to be open at the same time, even if the two workbooks are stored as distinct files in distinct folders
But wait (2) My ten-second experiment in my post suggests that Zoom factor (as it is defined today) is not stored with a workbook (I was not prompted to save the workbook).

I am missing something here, I believe. Nothing big is hanging on this (except my own persistent questioning of my own sanity!). :hairout:

Cheers
Chris
An expensive day out: Wallet and Grimace

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: avoiding select / activate

Post by StuartR »

I created the second window by choosing New Window from the View menu. Both of these windows are open in the same instance of Excel
StuartR


User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15499
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: avoiding select / activate

Post by ChrisGreaves »

StuartR wrote:
20 Sep 2021, 12:29
I created the second window by choosing New Window from the View menu. Both of these windows are open in the same instance of Excel
Thanks Stuart. I can do almost as well in Excel2003 by choosing Window, Split, although in my case a Zoom factor applies to both panes because I do not have two separate Window objects here (as per Hans's explanation).

That is a difference between Split and the new-fangled New Window.
I shall wait to hear why Ken doesn't use New Window, especially from VBA>

I read this explanation https://www.excelcampus.com/tips/new-window/ which does not address the VBA mechanism one might use.
Thanks again, Stuart
Chris
An expensive day out: Wallet and Grimace

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: avoiding select / activate

Post by HansV »

What Stuart says - you can open multiple windows in a workbook by selecting View > New Window.
You can see that there are multiple windows in the title bar:

S0768.png

The windows can display the same worksheet or different worksheets.
The windows and their view settings are stored with the workbook, but just changing the view settings without changing the workbook contents does not make Excel prompt to save changes.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
stuck
Panoramic Lounger
Posts: 8128
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: avoiding select / activate

Post by stuck »

ChrisGreaves wrote:
20 Sep 2021, 12:00
...I can't think of a reason why Ken shouldn't be able to code

Code: Select all

With Worksheets("newSheet")
        .Zoom = 90
        .SplitColumn = 0
        .SplitRow = 1
        .FreezePanes = True
End With
Nether could Ken but no matter what variation on that sort of syntax he tried, Excel VBA gave errors. That's why Ken asked here. Now Ken knows a bit more about Excel's object model and Ken is happy :groovin:

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: avoiding select / activate

Post by StuartR »

It's good to be happy
StuartR


User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15499
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: avoiding select / activate

Post by ChrisGreaves »

What Stuart says - you can open multiple windows in a workbook by selecting View > New Window. You can see that there are multiple windows in the title bar:
Thanks Hans; I see that now. In my Excel2003 I do not have View, new Window, which is why I stopped when I got to Window, Split, but I missed seeing Window, New, my eye gravitating to the familiar “split”. I have since tried with Window New and now see what Stuart sees.
Neither could Ken but no matter what variation on that sort of syntax he tried, Excel VBA gave errors. That's why Ken asked here. Now Ken knows a bit more about Excel's object model and Ken is happy
Right Ken! Excel VBA errors because Excel doesn’t support it. My “I don’t see why” was aimed petulantly at “Why wasn’t Excel designed to work this way?”. I had not written clearly.
If ken is happy, then I am happy.
It's good to be happy
Stuart I can’t think of a better reason for hanging around in Eileen’s Lounge!
Cheers
Chris
An expensive day out: Wallet and Grimace