Freeze Excel Panes with VBA

If you want to freeze panes programmatically, here's what you need to do.

I recorded a macro of myself freezing panes a few times manually. Here is the resulting code (scrubbed):

Sub Macro1()
    Range("A2").Select
    ActiveWindow.FreezePanes = True
    Range("E9").Select
    ActiveWindow.FreezePanes = True
End Sub

The trick (unfortunately) is you have to select the cell below and to the right of where you want to stop the worksheet from scrolling. If you try to freeze panes without selecting a cell, it just randomly freezes wherever it wants.

Here are two procedures that encapsulate the freezing panes function. You can either pass a Range object (the first macro) or a string representing a range (the second macro), but only use one, because your module won't compile if you put both of these in the same module (without renaming one).

Freeze panes with Range Object

Sub FreezePane(ByVal rng As Excel.Range)
' freeze panes of whatever range is passed

  On Error GoTo ErrorHandler

  rng.Select

  If TypeName(ActiveSheet) = "WorkSheet" Then
    ActiveWindow.FreezePanes = True
  End If

ProgramExit:
  Exit Sub

ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

Freeze panes with string Range

Sub FreezePane(ByVal rng As String)
' freeze panes of whatever range is passed

  On Error GoTo ErrorHandler

  Range(rng).Select

  If TypeName(ActiveSheet) = "WorkSheet" Then
    ActiveWindow.FreezePanes = True
  End If

ProgramExit:
  Exit Sub

ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

Sample usage:

Call FreezePane(Range("A2")) ' call first macro
Call FreezePane("A2") ' call second macro