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

TomCon

Active Member
Joined
Mar 31, 2011
Messages
373
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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).
 
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:

VBA Code:
With ActiveWindow
    Set FreezeCell = ActiveSheet.Cells(.Panes(1).ScrollRow + .SplitRow, .Panes(1).ScrollColumn + .SplitColumn)
End With
This is perfect, works exactly as was requested! If you just need the row or column number, the expressions in the ActiveSheet.Cells function work for that.
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top