vba: How do i figure out what cell at which panes are frozen with FreezePanes?

[TomCon]

Have looked into this a bit but do not see how to determine on which cell the panes are frozen if FreezePanes=True for a particular worksheet.

The ActiveWindow seems to have a SplitRow and SplitColumn property, but that is for Split Panes not FreezePanes.

The ActiveWindow.Pane has a VisibleRange property, but this depends on where the pane is scrolled to and does not necessarily indicate the intersecting cell of the panes.

What i want to find out is the cell that will be selected if you were to hit Ctl-Home in the user interface. But, this is recorded as something like Range("A2"). Select. In other words, I do not see a way to duplicate the behavior of Ctl-Home in code.

Any help with this? "how to find the cell at which the FreezePanes command was originally done on a worksheet" (in order to duplicate it elsewhere in code).

 

[footoo]

The following from : excel vba code for Ctrl+Home - Google Search

Application.Goto Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn), 1

 

[Jaafar Tribak]

Something like this maybe:

Sub Test()

   MsgBox Cells(FreezeRow(ActiveWindow), FreezeColumn(ActiveWindow)).Address

End Sub


Function FreezeRow(ByVal Wn As Window) As Long
    If Wn.FreezePanes Then
        FreezeRow = Wn.Panes(1).VisibleRange.Rows.Count + 1
    End If
End Function

Function FreezeColumn(ByVal Wn As Window) As Long
    If Wn.FreezePanes Then
        FreezeColumn = Wn.Panes(1).VisibleRange.Columns.Count + 1
    End If
End Function

 

[Jon Peltier]

Looks like .SplitRow and .SplitColumn return the row and column of the cell above and to the left of the freeze.

 

[TomCon]

The following from : excel vba code for Ctrl+Home - Google Search

Application.Goto Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn), 1

Thanks much! Did a few Google searches but did not phrase it as search for Ctl-Home. Too bad VBA does not record it that way!
But that simple 1-liner does the trick so it will serve the purpose in my code.
Thanks!

 

[Peter_SSs]

But that simple 1-liner does the trick so it will serve the purpose in my code.

Are you sure?
I think that will only work if you are already scrolled up & left as far as possible.
For example in the image below I had scrolled down and right some distance and then ran the post 2 code. The code took me to O35, not D5 which is where my Freeze Panes are.

 

[footoo]

Are you sure?
I think that will only work if you are already scrolled up & left as far as possible.
For example in the image below I had scrolled down and right some distance and then ran the post 2 code. The code took me to O35, not D5 which is where my Freeze Panes are.

View attachment 10607

This may work :

ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
Application.Goto Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn), 1

But I think Jaafar Tribak's suggestion is better.

 

[Jon Peltier]

If the windows was scrolled away from A1 before freezing panes (that is, the top left pane does not show cell A1), then Jafar's suggestion does not work.

Any suggestion that uses Application.Goto changes what the active cell is and where the main pane is scrolled to, and may disturb the user's experience.

We can modify Jafar's suggestion:

With ActiveWindow
    Set FreezeCell = ActiveSheet.Cells(.Panes(1).ScrollRow + .SplitRow, .Panes(1).ScrollColumn + .SplitColumn)
End With

 

[TomCon]

YES. Upon further testing in more circumstances....Indeed it turns out that the 1-line trick does not work always. It is dependent upon how the window happens to be scrolled. If the window is scrolled up and to the left (to rows 1,2,3,..and columns A,B,C...) it works. But if that is not the scroll of the window, then the 1-liner does not have the same behavior of Ctl-Home. I'll experiment with some of the others.

Thanks all for attention to this. Small thing in the UI (I use Ctl-Home all the time!) but harder to do in code.

 

[TomCon]

If the windows was scrolled away from A1 before freezing panes (that is, the top left pane does not show cell A1), then Jafar's suggestion does not work.

Any suggestion that uses Application.Goto changes what the active cell is and where the main pane is scrolled to, and may disturb the user's experience.

We can modify Jafar's suggestion:

With ActiveWindow
    Set FreezeCell = ActiveSheet.Cells(.Panes(1).ScrollRow + .SplitRow, .Panes(1).ScrollColumn + .SplitColumn)
End With

Yes, i confirmed that the above code will find the right cell regardless of where the window happened to be scrolled to before the code is executed. So, thank you very much!