Return Freeze panes location

[trevorc]

Hi All, another quandary. Is it possible to return the location of were the freeze panes was set. E.G. If I select say "D4" and freeze the panes, can I use VB to get that location so I can do something different than if the freeze panes was at say "G5".
What I'm trying to do is set the turn on/off the freeze panes via a button click, but want to always keep the top 2 rows frozen. the 2 states would be freeze panes at "3:3" or freeze panes "H4"
Here's my code so far...

Sub Freeze_Panes()
       curcel = ActiveCell.Address(0, 0)
    If ActiveWindow.FreezePanes = True Then
       ActiveWindow.FreezePanes = False
       ActiveSheet.Shapes("FP").TextFrame.Characters.Text = "Freeze" & vbCrLf & "Pane"
    Else
       Range("H4").Select
       ActiveWindow.FreezePanes = True
       ActiveSheet.Shapes("FP").TextFrame.Characters.Text = "Un-Freze" & vbCrLf & "Pane"
   End If
       Range(curcel).Select                 'select original cell
End Sub

[macropod]

Perhaps you could SendKeys to issue the Ctrl-Home command, then test the destination address.

[trevorc]

Thanks for the reply, I can see what your doing, but it seems a bit long winded for me, I did find that using .SplitRow And .SplitColumn I can determine the location faster, I will update my code when I get a chance.

[trevorc]

Turns out it was easy,

Sub Freeze_Panes() 'button click macro to freeze panes at row 4 and col A or Col H
    If ActiveWindow.SplitRow = 3 And ActiveWindow.SplitColumn = 7 Then
       ActiveWindow.SplitRow = 3
       ActiveWindow.SplitColumn = 0
       ActiveWindow.FreezePanes = True
       ActiveSheet.Shapes("FP").TextFrame.Characters.Text = "Freeze" & vbCrLf & "Pane"
    Else
       ActiveWindow.SplitRow = 3
       ActiveWindow.SplitColumn = 7
       ActiveWindow.FreezePanes = True
       ActiveSheet.Shapes("FP").TextFrame.Characters.Text = "Un-Freze" & vbCrLf & "Pane"
   End If
End Sub

[macropod]

Simpler:

Sub Freeze_Panes() 'button click macro to freeze panes at row 4 and col A or Col H
With ActiveWindow
  .SplitRow = 3
  If .SplitColumn = 7 Then
    .SplitColumn = 0
    ActiveSheet.Shapes("FP").TextFrame.Characters.Text = "Freeze" & vbCr & "Pane"
  Else
    .SplitColumn = 7
    ActiveSheet.Shapes("FP").TextFrame.Characters.Text = "Un-Freeze" & vbCr & "Pane"
  End If
  .FreezePanes = True
End With
End Sub

[trevorc]

Yes it is, which made me think this...

Sub Freeze_Panes() 'button click macro to freeze panes at row 4 and col A or Col H
    If ActiveWindow.SplitColumn = 7 Then
       ActiveWindow.SplitColumn = 0
       ActiveSheet.Shapes("FP").TextFrame.Characters.Text = "Freeze" & vbCrLf & "Pane"
    Else
       ActiveWindow.SplitColumn = 7
       ActiveSheet.Shapes("FP").TextFrame.Characters.Text = "Un-Freze" & vbCrLf & "Pane"
   End If
       ActiveWindow.FreezePanes = True
End Sub

[macropod]

I prefer the With … End With structure. More importantly, though, your omission of 'ActiveWindow.SplitRow = 3' means you're no longer controlling the possibility that someone might have manually changed the row split.

[trevorc]

I'm the only user so not a problem, and the macro button is only on the ActiveWindow, so don't need the With...End With.