Solved: activewindow.panes(i)

[TheAntiGates]

Can anyone list an online resource on panes? I'm having a hard time. In particular I'm having trouble understanding where panes 2 and 3 are in a 4 pane scenario.

[tstav]

Say you have a horizontal split bar on row 10. This gives an upper pane and a lower pane.
If you add a vertical split bar (e.g. on column 5) you get a total of 4 panes.
Upper left is number 1
Upper right is number 2
Lower left is number 3
Lower right is number 4.

Is that OK?

[TheAntiGates]

In some workborks 2 is upper right but it appears that 3 is upper right in others. I can send you one if you PM an email address, or instruct where I could post it. I'm on XL03.

The deal is that with a 4 pane split (thick separators), and scroll row 15 at the bottom,
[vba]?ActiveWindow.Panes(2).ScrollRow[/vba]
shows 1 in the immediate window.
If you then immediately issue
[vba]
ActiveWindow.Panes(2).ScrollRow=1
[/vba]nothing SHOULD happen - but the lower panes scroll to row one.

I haven't come up with a way to create this from scratch other than in the specific worksheet I'm experimenting with.

[tstav]

Why PM the workbook and not post it here for all to see and help?
The more the better, isn't it so?

[TheAntiGates]

or instruct where I could post it.

I see insert link but not insert file.

[tstav]

As post#14 says, the results of pane-numbering, depend on which splitbar was created first: The horizontal or the vertical.


<Edit>: This post was corrected after results showing in post#14, so as not to mislead.

[tstav]

Changing the scrollrow of Pane 1 or 3 will produce the same result, since they are both referring to the upper pane.
Then test the scrollcolumn of pane 3 (or 4) and you will see that they are both referring to the right-hand panes.

[TheAntiGates]

(Moderator: please delete the previous post. I don't have the option to delete.)

Okay, I found the attachment "clip." Yes, and sorry, my attachment has a single macro. It's the only way I can produce the problem currently.

Okay, in code the instance 1, pane 2 info is read into elements 14 and 15 of the array. Then instance 2, pane 2 are set from those elements. It's as though it instance one has pane 2 on UPPER RIGHT.

Repeating a prior post, you can go [vba]?ActiveWindow.Panes(2).ScrollRow[/vba]
and then immediately issue [vba]ActiveWindow.Panes(2).ScrollRow=1[/vba]at the point of the msgbox. Weird.

[Simon Lloyd]

TheAntiGates, why delete your post?, also when posting any VBA code please surround it with the VBA code tags by highlighting your code and clicking the green VBA button at the top of your Edit/New Post window.

[TheAntiGates]

If it's such a big deal, then rather than delete the post, delete the thread.

If tags are required for 3 or less lines of code I'm sorry - just delete the thread.

Really, delete it. You don't want this evidence to remain - for your own benefit.

The purpose of deleting the post was to replace the attachment with a more useful one. Buy why are you even asking? You go to the trouble of editing 2 silly SINGLE lines of code and STILL don't delete the post.


*Questionable Content Remove by Oorang*

[TheAntiGates]

And by the way, NEITHER of the commands were VBA, per se. They were immediate window commands. Ergo, the question mark, whose purpose you destroyed by using the tags.

?sheets.count

does NOT get tags. *Questionable Content Remove by Oorang*

[Simon Lloyd]

There was no malice in my request.....there was a simple question and simple request so why take offense and be personal about it? i have been nothing but curteous! If the "code" you post comes from the VBE then it is easier read inside the code tags.

Please get a grip!

[tstav]

I hope this is going to cool things down...

AntiGates, I think the solution to your puzzle (indeed a puzzle) lies with the sequence of creating the splitbars. It makes a difference which splitbar we create first.

1. Create the horizontal first and then the vertical and you have:
Upper Left = 1
Lower Left = 2
Upper Right = 3
Lower Right = 4

2. Create the vertical first and then the horizontal and you have:
Upper Left = 1
Upper Right = 2
Lower Left = 3
Lower Right = 4

I deleted the splitbars of your file and recreated them following the sequence number 1. The panes "worked" as is shown in sequence number 1 above.

Then I deleted the splitbars of your file again and recreated them following the sequence number 2. The panes "worked" as it is shown in sequence number 2 above.

[TheAntiGates]

Outstanding deduction. Excellent work. Now, the interesting part is to determine "which was created first" in an existing sheet (in my example, window instance 1, which I wish to clone as instance 2).

Just thinking out loud: I suppose comparing .ScrollRow between Pane 1 and 2 would do it - nope, all 4 panes might have the same .ScrollRow, so that wouldn't be definitive. Sadly, the same goes for .ScrollColumn. I wonder what would be definitive.

For my immediate purposes (to clone the source sheet), I suppose that if the scroll properties of pane 2 and 3 are equal, it doesn't matter for my current project. I wonder what - if any - other pane properties might reveal the "creation order" you well explained, though, in order to truly identify which pane is which on an existing sheet (that is, determining if pane 2 is on upper right or lower left).

I'm marking this solved anyway. Maybe as a bonus that information will appear. If I determine anything, I'll get the information here.

Thank you very kindly. As I'm sure you can appreciate, the so-to-speak "inconsistency" is/was very frustrating. Now I understand that there is a very good logical explanation. Clearly you spent some time in experimentation to arrive at your deduction. Thank you for hanging in there and moving forward. I owe you for solving a very long time, quite perplexing issue.

Simon, I apologize for being personal. You ARE a good guy. I still believe you were just wrong. You should have killed the post, which is simply misleading and time-consuming for anyone who uses it, as I asked; and not futzed with other posts by adding tags, which only hurt the flow. I very specifically chose no tags and you bulldozed right in. But I know you meant well, and that you usually aid threads that are a mess without them.

[TheAntiGates]

I think the solution to your puzzle (indeed a puzzle) lies with the sequence of creating the splitbars. It makes a difference which splitbar we create first.

What's bothersome about that is splitting the window by issuing the menu command alt-W-F (i.e. perform the split/freeze) from, e.g., 4 columns in from the left and r rows down from the top. Who knows whether the horizontal is created first then?! I hope that Excel versions are at least consistent in how they handle that!!

[TheAntiGates]

For my purposes the following is an adequate determinant of the pane orientation. Deeply grateful credit to tstav for inspiring this solution (or workaround, as it still feels flimsy).[vba]If (ActiveWindow.Panes(1).ScrollRow = ActiveWindow.Panes(2).ScrollRow) _
And (ActiveWindow.Panes(1).ScrollColumn = ActiveWindow.Panes(3).ScrollColumn) Then
bColFirst = True 'presume that pane 2 is upper right
end if[/vba]Later on,[vba]if bColFirst then ActiveWindow.SplitColumn = [some column value]
ActiveWindow.SplitRow = [some row value]
if not bColFirst then ActiveWindow.SplitColumn = [some column value][/vba]or less compactly and more traditionally

if bColFirst then 
    ActiveWindow.SplitColumn = [some column value]
    ActiveWindow.SplitRow = [some row value]
else
    ActiveWindow.SplitRow = [some row value]
    ActiveWindow.SplitColumn = [some column value]
end if

[Simon Lloyd]

No apology needed i deal with "frustrated" people all the time!

[rory]

I would have thought that all you need to know is whether there are 1,2 or 4 panes. 1 or 2 are obviously easy to work with, and if there are 4, then you can always use either pane 1 or pane 4 for scrolling rows or columns without needing needing to know where 2 or 3 are, since 1 is always top and left, and 4 is always bottom and right.
And, IMO, both commands were VBA; VBA is VBA no matter where you enter it.

[TheAntiGates]

rory,
The purpose of identifying pane orientation is illustrated in the earlier attachment. ActiveWindow.NewWindow (at least through Excel 11) misses horribly in reproducing the source properties. Zoom, splits, and other features are all lost in the new instance. (And the really tragic part is if you then close the first instance before the second, those properties are gone forever - you better have a good backup, or lose all your current session.)

So when you use .NewWindow you have to set splits yourself (or lose them there). Ergo, the need to identify whether the 4 pane orientation is "left-right, down, left-right" or "up-down, right, up-down" - i.e., is pane 2 upper right or lower left - for setting pane 2 properties. As tstav identified, if you read pane 2 properties from the source window, you need to recognize that the .NewWindow copy may need to set those properties on pane 3!

[rory]

Which properties are you trying to set that you can't do using 1 and 4? If you set pane 1's scroll row, then the top right pane's scroll row is set (doesn't matter what its index is) and if you set pane 4's scroll row, then the bottom left row is set, again no matter what its index. And vice versa for scroll column. Or have I missed something?