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
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 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
' 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
Call FreezePane("A2") ' call second macro
