Excel VBA Freeze Worksheet: Freeze Panes or Lock Cells

In Excel VBA, use ActiveWindow.FreezePanes to keep rows or columns visible while you scroll; use worksheet protection when you mean to stop people editing cells. These are different features, so the right method depends on whether you want a fixed on-screen header or a sheet that users cannot change.

What “freeze worksheet” means in Excel

Excel does not have one VBA command that freezes an entire worksheet in every sense of the phrase. People commonly mean one of two things: freeze panes, which keeps selected rows or columns in view as the user scrolls, or protect a worksheet, which restricts changes to cells and other sheet content. A frozen pane does not make data read-only, and sheet protection does not keep headings visible.

Freeze panes are a view setting associated with an Excel window. They are useful in long tables where column labels or identifying fields would otherwise scroll off-screen. Worksheet protection is an editing control: it can prevent changes to locked cells while allowing permitted actions, such as editing unlocked input cells. You can use both features in the same workbook when you need fixed headings and controlled editing.

Freeze rows and columns with VBA

To freeze panes using VBA, first make the worksheet active, select the cell just below the rows and to the right of the columns you want to keep visible, and then set the active window’s FreezePanes property to True. For example, selecting A2 freezes the first row. Selecting B2 freezes both the first row and the first column.

The following macro freezes the top row on a worksheet named Data. It first clears any existing frozen panes in the active window, so running it again produces a predictable result.

Sub FreezeTopRow()
    Dim ws As Worksheet

    ' Change Data to the worksheet tab name you want to use.
    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Activate

    ' Remove an existing freeze, then freeze the row above A2.
    ActiveWindow.FreezePanes = False
    ws.Range("A2").Select
    ActiveWindow.FreezePanes = True
End Sub

To freeze the top row and first column together, change the selected cell from A2 to B2. To freeze the first two rows, select A3. To freeze the first two rows and first column, select B3. The rule is that panes above and to the left of the selected cell remain fixed; the selected cell itself belongs to the scrollable area.

Before running the macro, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, and a macro may replace a pane arrangement you had set up manually.

Run the macro and save it correctly

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. On a Mac, Option+F11 is the usual shortcut; some keyboards also require Fn.
In the editor, choose Insert > Module, then paste the procedure into the module.
Replace Data with the exact worksheet tab name. The name must match, including spaces and punctuation.
Return to Excel and run the macro from the Visual Basic Editor, or use Developer > Macros, select FreezeTopRow, and choose Run.
Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) if you want to keep the VBA code.

If the Developer tab is not visible, enable it through File > Options > Customize Ribbon on Windows. On Mac, use Excel > Settings (or Preferences, depending on the release) > Ribbon & Toolbar. Macro availability and security settings can vary by organization; only enable macros in files you trust. Excel for the web can open macro-enabled workbooks but cannot create, run, or edit VBA macros, so use the desktop Excel app for these steps.

Freeze panes versus worksheet protection

Use freeze panes when the problem is navigation: users need to see headers or key identifying columns while moving through a large sheet. Use protection when the problem is accidental or unauthorized editing. Protection does not encrypt a workbook or provide strong security for confidential information; it is primarily a way to control routine changes within a worksheet.

To protect a sheet through the ribbon, open the worksheet and choose Review > Protect Sheet. Select the actions users should be allowed to perform, optionally enter a password, and confirm. A cell’s Locked setting takes effect only after sheet protection is turned on. Most cells are locked by default, so if users should fill in specific cells, unlock those cells first using Format Cells > Protection and clear Locked, then protect the sheet.

A simple VBA example protects a worksheet named Data. It assumes that the cells you want users to edit have already been unlocked. Replace the example password with an appropriate value, or omit the Password argument if you do not want to set one.

Sub ProtectDataSheet()
    Dim ws As Worksheet

    ' Change Data to the worksheet tab name you want to protect.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Protect the sheet; unlocked cells remain available for input.
    ws.Protect Password:="ChangeThisPassword"
End Sub

To remove protection in VBA, use the worksheet’s Unprotect method and supply the password if one was set. Keep in mind that forgetting a password can make normal editing difficult. Do not treat worksheet protection as a substitute for file access controls or secure storage.

How Excel applies the freeze setting

FreezePanes belongs to the active window, not directly to a worksheet object. That detail explains why a macro activates the target sheet before selecting a cell and setting the property. Excel needs an active window and selection to determine where the pane boundary goes. If a workbook has multiple windows open, the active window matters; a macro may not affect the window the user expected if another window is active.

Freeze panes also depend on the current selection and any existing split arrangement. Setting FreezePanes to False clears the frozen-pane state in that window before the macro creates a new one. If you do not clear it, the outcome can be confusing when the window already has frozen panes or splits. For a macro intended for repeated use, explicitly setting the desired state is generally easier to understand than relying on whatever view happens to be active.

Freezing panes changes how the worksheet is displayed, not the worksheet’s data, formulas, or print layout. It does not insert rows, hide columns, or permanently pin headings for every recipient in every viewing environment. The view is saved with the workbook, but users can change their own view settings.

Common problems and practical fixes

Run-time error on the worksheet name: Check that the tab is actually named Data, or edit the quoted name in the code. A worksheet’s tab name is not necessarily the same as its VBA code name.
The wrong row or column stays visible: Select the cell below and to the right of the intended frozen area. For the first row only, use A2; for the first column only, use B1; for both, use B2.
The macro runs but the view looks unchanged: Make sure the intended workbook window is active and that the macro activated the correct worksheet. Also check whether the sheet is displayed in a view where the expected pane behavior is apparent.
The macro is missing after reopening: Save as .xlsm rather than .xlsx. The .xlsx format does not retain VBA code.
Excel blocks the macro: Review the Trust Center or organizational macro policy. Do not lower security settings indiscriminately or enable code from an unknown source.
Users can still edit cells: Freeze panes does not restrict editing. Protect the worksheet, and verify that the relevant cells are locked before protection is enabled.

When troubleshooting, test on a copy and run the procedure with the target sheet visible. If a workbook is shared among people using different Excel versions or platforms, explain the intended view and provide a clear way to change it; a macro is not required just to use Freeze Panes from the View tab.

Choosing the simplest method

A macro is useful when a workbook should apply the same view every time, when several sheets need standardized headers, or when a larger automation routine prepares a report. For a one-time adjustment, use the Excel interface: select the appropriate cell, then choose View > Freeze Panes > Freeze Panes. The same menu offers Freeze Top Row and Freeze First Column, which are convenient when you only need one of those common layouts.

For a table with headers, another option is to format the data as an Excel table using Home > Format as Table. Table headers can remain available as you scroll within the table, which may suit a structured list without configuring worksheet panes. This is not identical to freezing worksheet rows: it applies to the table’s header behavior, and it does not pin arbitrary rows or columns outside that table.

On Mac, the ribbon layout can differ slightly by Excel release, but the Freeze Panes command is available from the View tab in desktop Excel. Excel for the web provides view controls, but VBA procedures do not run there. If the workbook must work in a browser, set up the view in desktop Excel and do not depend on a macro to apply or change it in the web app.

Frequently Asked Questions

Can VBA freeze the top row and first column at the same time?
Yes. Activate the worksheet, select cell B2, and set ActiveWindow.FreezePanes to True. The top row and first column are above and to the left of B2, so they remain visible while the rest of the sheet scrolls.

What cell should I select to freeze the first three rows?
Select A4 before turning on Freeze Panes. The rows above the selected cell are frozen, so rows 1 through 3 stay visible. If you also want the first column fixed, select B4 instead.

Does freezing a worksheet stop people from changing it?
No. Freeze panes is a display feature only. To restrict editing, unlock any cells users should be able to change, then use Review > Protect Sheet or the worksheet’s VBA Protect method.

Can I freeze panes in Excel for the web with VBA?
No. Excel for the web does not run VBA macros. Use the desktop Excel app to run the macro; browser users can work with supported view controls but cannot run that VBA code.

How do I unfreeze panes with VBA?
Activate the relevant worksheet window and set ActiveWindow.FreezePanes to False. This removes the frozen panes in that active window; it does not unprotect the sheet or change its cells.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.