Excel Worksheet Freeze and Zoom Macros

Sample macros to freeze, unfreeze or zoom all sheets in the active workbook

Freezing Worksheets

Use this macro to freeze all the sheets in the active workbook. Before you run the macro, select a cell, row or column, below and to the right of the area that you want frozen.

Here's what will be frozen, based on your selection:

Selection	Frozen Area
Cell A1	All cells above and to the left of center
Other cell in column A	All rows above that cell
Other cell in row 1	All columns to the left of that cell
Other cell	All cells above and to the left of the selected cell
Row 1	All cells above and to the left of center
Any other row	All rows above that row
Column A	All cells above and to the left of center
Any other column	All columns to the left of that column

Freeze All Worksheets Macro

Use this macro to freeze all the sheets in the active workbook. Before you run the macro, select a cell, row or column, below and to the right of the area that you want frozen. See details in the chart in the previous section.

When the macro starts, it will show a confirmation message. Click Yes to go ahead with the unfreezing, and click No to cancel. In this screen shot, row 5 is selected, so rows 1 to 4 will be frozen on all sheets, if the Yes button is clicked.

confirmation to unfreeze all sheets

Copy this macro into a regular code module, then select cell(s) on any worksheet, and run the macro to freeze all the sheets.

Sub FreezeAllSheets()
'www.contextures.com
Dim wsA As Worksheet
Dim ws As Worksheet
Dim wbA As Workbook
Dim strSel As String
Dim lRsp As Long
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strSel = Selection.Address

lRsp = MsgBox("Freeze all sheets at current selection?", _
  vbQuestion + vbYesNo + vbDefaultButton1, "Freeze Sheets?")

If lRsp = vbYes Then
  Application.ScreenUpdating = False
  For Each ws In wbA.Worksheets
    ws.Activate
    Range(strSel).Select
    ActiveWindow.FreezePanes = True
  Next ws
  wsA.Activate
Else
  'do nothing
End If

exitHandler:
  Application.ScreenUpdating = True
  Exit Sub
errHandler:
  MsgBox "Could not freeze all sheets"
  Resume exitHandler

End Sub

Unfreeze All Worksheets Macro

Use this macro to unfreeze all the sheets in the active workbook. It doesn't matter which worksheet or which cell is selected before you run this macro.

When the macro starts, it will show a confirmation message. Click Yes to go ahead with the unfreezing, and click No to cancel.

confirmation to unfreeze all sheets

Copy this macro into a regular code module, and run the macro to unfreeze all the sheets.

Sub UnfreezeAllSheets()
'www.contextures.com
Dim wsA As Worksheet
Dim ws As Worksheet
Dim wbA As Workbook
Dim lRsp As Long
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet

lRsp = MsgBox("Unfreeze all sheets?", _
  vbQuestion + vbYesNo + vbDefaultButton1, "Unfreeze Sheets?")

If lRsp = vbYes Then
  Application.ScreenUpdating = False
  For Each ws In wbA.Worksheets
    ws.Activate
    ActiveWindow.FreezePanes = False
  Next ws
  wsA.Activate
Else
  'do nothing
End If

exitHandler:
  Application.ScreenUpdating = True
  Exit Sub
errHandler:
  MsgBox "Could not unfreeze all sheets"
  Resume exitHandler

End Sub

Zoom All Worksheets Macro

You can manually change the zoom setting for any worksheet, to a number between 10 and 400. If you want to set all the worksheets to the same zoom level, use this macro.

When the macro starts, it will show an input box, where you can enter a zoom level. The default is 100. Click OK to go ahead with setting the zoom level, or click Cancel. In this screen shot, 100 is entered as the zoom setting, so all sheets will have that zoom level, if the OK button is clicked.

enter a zoom setting

Copy this macro into a regular code module, then run the macro to set the zoom level for all the sheets.

Sub ZoomAllSheets()
Dim wsA As Worksheet
Dim ws As Worksheet
Dim wbA As Workbook
Dim varZoom As Variant
Dim lZoom As Variant
Dim strMsg As String
Dim strValid As String
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strMsg = "Enter a zoom setting" _
  & vbCrLf _
  & "between 10 and 400"
strValid = "Zoom must be a number" _
  & vbCrLf _
  & "between 10 and 400"

varZoom = InputBox(strMsg, "Zoom", 100)
If Len(varZoom) = 0 Then
  GoTo exitHandler
End If

If IsNumeric(varZoom) Then
  lZoom = CLng(varZoom)
Else
  MsgBox strValid
  GoTo exitHandler
End If

If lZoom > 10 And lZoom < 400 Then
  Application.ScreenUpdating = False
  For Each ws In wbA.Worksheets
    ws.Activate
    ActiveWindow.Zoom = lZoom
  Next ws
  wsA.Activate
Else
  MsgBox strValid
  GoTo exitHandler
End If

exitHandler:
  Application.ScreenUpdating = True
  Exit Sub
errHandler:
  MsgBox "Could not freeze all sheets"
  Resume exitHandler

End Sub

Download the Sample File

Click here to download the Zoom and Freeze Macros sample file. The file is zipped, and it is in xlsm format, and contains macros. Be sure to enable macros when you open the workbook, if you want to test the macros.