How to freeze panes in a specific sheet using VBA?

To freeze panes in a specific sheet using VBA, you can follow these steps. The example below demonstrates how to freeze panes in an Excel worksheet programmatically using VBA. This example assumes you want to freeze the panes above and to the left of a specific cell, typically cell `A2` to keep the first row visible as you scroll down.

Open Excel and Press `ALT + F11` to open the VBA editor.
Insert a New Module:

Click on `Insert` in the toolbar.
Choose `Module` from the dropdown. This will insert a new module in the project.

Write the VBA Code:

Here is a sample code that freezes panes based on cell `A2` in a specific sheet named “Sheet1”:

   Sub FreezePanesInSpecificSheet()

       ' Define the worksheet you want to work with
       Dim ws As Worksheet
       Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your specific sheet name

       ' Select the sheet
       ws.Activate

       ' Define the cell where you want to freeze panes. A2 will freeze the first row
       ws.Range("A2").Select

       ' Apply freeze panes
       ActiveWindow.FreezePanes = True

   End Sub

Run the VBA Code:

Place your cursor within the code.
Press `F5` to run the macro, or go to `Run > Run Sub/UserForm`.

Note:

Ensure that “Sheet1” is replaced with the actual name of your sheet.
Adjust the `Range(“A2”)` if you want to freeze at a different cell.

This script activates the specified worksheet, selects the cell at which you want to freeze panes, and then applies the freeze panes operation. If you’re working with a workbook in a language other than English, make sure the sheet name matches exactly with what’s shown in the Excel interface.

facebooklinkedin