Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reference to cell with text is returning #VALUE | Excel Discussion (Misc queries) | |||
Cell reference returning incorrect value | Excel Discussion (Misc queries) | |||
Returning reference of frozen cell | Excel Programming | |||
Returning a cell reference with a formula? | Excel Worksheet Functions | |||
Returning with cell on right/left of a reference | Excel Worksheet Functions |