Returning reference of frozen cell
 

I have a spreadsheet in which the Freeze Panes anchor may be set by a user.
I would like to know the cell location of where the freeze panes is, without
selecting any cells. How do I write code to check this?

Ideally, what I'm trying to do is something like this:

x = ActiveWindow.FreezePanes.Row
y = ActiveWindow.FreezePanes.Column

....except that "ActiveWindow.FreezePanes.Row" and
"ActiveWindow.FreezePanes.Column" is not a valid expression.

Would appreciate any help! Thanks.

Returning reference of frozen cell
 

I have a spreadsheet in which the Freeze Panes anchor may be set by a user.
I would like to know the cell location of where the freeze panes is,
without
selecting any cells. How do I write code to check this?

Ideally, what I'm trying to do is something like this:

x = ActiveWindow.FreezePanes.Row
y = ActiveWindow.FreezePanes.Column

...except that "ActiveWindow.FreezePanes.Row" and
"ActiveWindow.FreezePanes.Column" is not a valid expression.

Would appreciate any help! Thanks.

I have never played around with this stuff myself, but a search through the
help files yielded this... ActiveWindow.SplitRow and
ActiveWindow.SplitColumn. There was also mention of panes and this seemed to
yield something positive (but I am unsure of how many panes are possible in
total)...

ActiveWindow.Panes(ActiveWindow.Panes.Count).Scrol lRow

ActiveWindow.Panes(ActiveWindow.Panes.Count).Scrol lColumn

where the scrollable area seems to be in the highest numbered pane (hence my
use of the count property).

Hope this helps.

Rick

Returning reference of frozen cell
 

Dim X as long
dim Y as long
With ActiveWindow.Panes(4)
Y = .ScrollColumn
X = .ScrollRow
End With




GilesT wrote:

I have a spreadsheet in which the Freeze Panes anchor may be set by a user.
I would like to know the cell location of where the freeze panes is, without
selecting any cells. How do I write code to check this?

Ideally, what I'm trying to do is something like this:

x = ActiveWindow.FreezePanes.Row
y = ActiveWindow.FreezePanes.Column

...except that "ActiveWindow.FreezePanes.Row" and
"ActiveWindow.FreezePanes.Column" is not a valid expression.

Would appreciate any help! Thanks.

--

Dave Peterson

Returning reference of frozen cell
 

Dim X as long
dim Y as long
With ActiveWindow.Panes(4)
Y = .ScrollColumn
X = .ScrollRow
End With

From my quick experiment with this "pane" stuff, I concluded using the
hard-coded 4 might fail if the spreadsheet were only split at the columns
only or the rows only (there would only be 2 panes then). My solution,
applied to your setup would be something like this...

Dim X as long
Dim Y as long
Dim MaxPane as Long
MaxPane = ActiveWindow.Panes.Count
With ActiveWindow.Panes(MaxPane)
Y = .ScrollColumn
X = .ScrollRow
End With

Rick

Returning reference of frozen cell
 

Dim X as long
dim Y as long
With ActiveWindow.Panes(4)
Y = .ScrollColumn
X = .ScrollRow
End With

From my quick experiment with this "pane" stuff, I concluded using the
hard-coded 4 might fail if the spreadsheet were only split at the columns
only or the rows only (there would only be 2 panes then). My solution,
applied to your setup would be something like this...

Dim X as long
Dim Y as long
Dim MaxPane as Long
MaxPane = ActiveWindow.Panes.Count
With ActiveWindow.Panes(MaxPane)
Y = .ScrollColumn
X = .ScrollRow
End With

Actually, more in keeping with your original structure and the With
statement block, perhaps this instead...

Dim X As Long
Dim Y As Long
With ActiveWindow.Panes
Y = .Item(.Count).ScrollColumn
X = .Item(.Count).ScrollRow
End With

Although we lose the self-documenting MaxPane variable name this way.

Rick

Returning reference of frozen cell
 

I agree with you.



"Rick Rothstein (MVP - VB)" wrote:

Dim X as long
dim Y as long
With ActiveWindow.Panes(4)
Y = .ScrollColumn
X = .ScrollRow
End With

From my quick experiment with this "pane" stuff, I concluded using the
hard-coded 4 might fail if the spreadsheet were only split at the columns
only or the rows only (there would only be 2 panes then). My solution,
applied to your setup would be something like this...

Dim X as long
Dim Y as long
Dim MaxPane as Long
MaxPane = ActiveWindow.Panes.Count
With ActiveWindow.Panes(MaxPane)
Y = .ScrollColumn
X = .ScrollRow
End With

Rick

--

Dave Peterson