Sample macros to freeze, unfreeze or zoom all sheets in the active workbook
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 |
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.
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
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.
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
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.
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
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.
Last updated: April 20, 2021 4:16 PM