PDA

View Full Version : Solved: activewindow.panes(i)



TheAntiGates
04-23-2008, 09:44 AM
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
04-23-2008, 10:43 AM
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
04-23-2008, 11:32 AM
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,
?ActiveWindow.Panes(2).ScrollRow
shows 1 in the immediate window.
If you then immediately issue

ActiveWindow.Panes(2).ScrollRow=1
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
04-23-2008, 11:40 AM
Why PM the workbook and not post it here for all to see and help?
The more the better, isn't it so?

TheAntiGates
04-23-2008, 11:47 AM
or instruct where I could post it.I see insert link but not insert file.

tstav
04-23-2008, 11:49 AM
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
04-23-2008, 11:53 AM
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
04-23-2008, 12:12 PM
(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 ?ActiveWindow.Panes(2).ScrollRow
and then immediately issue ActiveWindow.Panes(2).ScrollRow=1at the point of the msgbox. Weird.

Simon Lloyd
04-23-2008, 12:32 PM
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
04-23-2008, 12:45 PM
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
04-23-2008, 12:50 PM
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
04-23-2008, 12:58 PM
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
04-23-2008, 01:18 PM
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
04-23-2008, 02:58 PM
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. :bow:

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
04-23-2008, 03:08 PM
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
04-23-2008, 03:42 PM
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).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 ifLater on,if bColFirst then ActiveWindow.SplitColumn = [some column value]
ActiveWindow.SplitRow = [some row value]
if not bColFirst then ActiveWindow.SplitColumn = [some column value]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
04-23-2008, 04:14 PM
No apology needed i deal with "frustrated" people all the time!

rory
04-24-2008, 05:30 AM
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
04-24-2008, 07:48 AM
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
04-24-2008, 08:05 AM
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?

TheAntiGates
04-24-2008, 11:26 AM
Well I have to admit it sounds like you have smartly simplified the whole deal. 1 and 4 forces 2 and 3. Of course! Good one!

My whole paradigm was to set pane 1, 2, and 3, thus letting 4 take care of itself. I never thought about doing 1 and 4, letting 2 and 3 take care of THEMselves. Lucky for me, you've looked past "linear thinking" in a sense :clap:

If I later recognize any deficiency from that approach I'll report it. It's unlikely, but Microsoft is involved so I'll slightly hedge!

Thank you for very useful insight.

tstav
04-24-2008, 01:21 PM
I really don't understand what it is that you guys have managed to agree upon. I don't see what good it does to set the properties of panes 1 and 4 and "let panes 2 and 3 take care of themselves", when we still have no way of telling which pane is the top-right one (or bottom-left) no matter how we set their scrollRow and scrollColumn properties. That's what my thick head insists on telling me...

What I mean: I'm splitting the sheet to 4. I'm not telling you how I do it. I give you the scrollRow and scrollColumn properties of both panes 1 and 4. They are all yours to know. I ask you to fill a cell in pane 2 with any color you want. Can you do it (in code)?

I can't.

If I don't know in what sequence was the sheet split to 4 panes, there is no way of telling which pane is 2 and which is 3. SplitLines' sequence is not traceable. It would be if there was a collection like SplitLines, whose index property would let us know the information missing.

e.g. "If SplitLines(1) = xlHorizontal Then" (something like that).

Am I wrong???

<Edit:> Mind you, with pane 2 I mean the top-right pane. Sorry for the mistake.

tstav
04-24-2008, 01:28 PM
Mind you, with pane 2 I mean the top-right pane. Sorry for the mistake.

rory
04-24-2008, 01:29 PM
the point is that we don't need to know whether 2 is top right or bottom left. If I want to fill a cell in pane 2, I can reference Panes(2). If I want to fill a cell in the top right pane, I can get a column ref from pane 4 and a row ref from pane 1. The intersection will be in the top right pane.

tstav
04-24-2008, 01:37 PM
What a relief... Intersection. Yes! End of story. Thanks rory!
Good night everybody. It's midnight here.