Freeze panes with VBA

Question

I am able to freeze the first 2 rows and first 4 columns using:

ActiveWindow.FreezePanes = False
With ActiveWindow
    .SplitColumn = 4
    .SplitRow = 2
    End With
ActiveWindow.FreezePanes = True

However this only seems to work if I am in the sheet I want to freeze. But The Command button is in a different sheet, and I would rather not change it. Is there a way of specifying which sheet I want frozen? I would also be happy with freezing all sheets the same way if required.

Thanks

hi. u have everywhere ActiveWindow... try to change to the sheet u want... — Luis Curado, Oct 22 '19 at 17:02
Possible duplicate of [excel vba freeze pane without select](https://stackoverflow.com/questions/34094445/excel-vba-freeze-pane-without-select) — Sam, Oct 22 '19 at 17:17

score 0 · Answer 1 · answered Oct 22 '19 at 17:14

You could do something like this... (See comments for explanation)

' Don't update the screen
Application.ScreenUpdating = False
Dim wb As Workbook
Dim ws As worksheet
' Change this if you aren't using the workbook your code is in
Set wb = ThisWorkbook
' Activate the workbook... otherwise you might not be able to activate the sheet
wb.Activate
' Save the current sheet
Set ws = wb.ActiveSheet
' The sheet to apply FreezePanes to
wb.Sheets("Your Sheet").Activate
With ActiveWindow
    .FreezePanes = False
    .SplitColumn = 4
    .SplitRow = 2
    .FreezePanes = True
End With
' Move back to original sheet
ws.Activate
' Update the screen again
Application.ScreenUpdating = True

Freeze panes with VBA

1 Answers1