VBA – Freeze Panes

Freeze Panes Using VBA

I recently posted on Freezing panes in Excel, here’s how you can do it using VBA:

Freeze Rows

Rows("1:1").Select

ActiveWindow.FreezePanes = True

Freeze Columns

Range("A:A").Select

ActiveWindow.FreezePanes = True

Freeze Rows and Columns

Range("B2").Select

ActiveWindow.FreezePanes = True

UnFreeze Panes

ActiveWindow.FreezePanes = False

Force Workbook to Save Without Freeze Panes

Excel lets us control things through events. This article isn’t a detail discussion on what events are or their features. Instead it gives an example of a WorkBook Event. These are events that are held at the workbook level rather than a particular worksheet.

Through VBA we can control what happens at certain events such as before printing or before saving. One common problem that I face is that a number of people I work with don’t like files with Freeze Panes on them.

So in this article, we will put together some code that will check if Freeze Panes is on and if so, it won’t save the file. This means that I have to save it without freeze panes – keeping my colleagues happy !!

The most important thing about workbook events is that they should be saved in the correct place – at the workbook level.

To access the workbook level, follow the steps below:

1. Right click on an Excel workbook – view code:

2. This will bring up:

3. Double click on “This Workbook” and then select “WorkBook” from the first drop down on the left hand side:

We see that the value on the left hand side has now changed to “Open” – with some code for the Workbook Open Event. This code will let us determine what happens when the workbook opens for the first time.

However we want to control what happens when we save the workbook. So change the right hand drop down to “Before Save” . The screen will now look like:

We now insert the following code after the declaration:

If ActiveWindow.FreezePanes = True Then

    MsgBox "Freeze Panes is on - File is NOT SAVED"

    Cancel = True

End If

So that the complete code now looks like:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If ActiveWindow.FreezePanes = True Then

    MsgBox "Freeze Panes is on - File is NOT SAVED"

    Cancel = True

End If

End Sub

Now save the file and THEN activate Freeze Panes in any window. Then – RESAVE the file. A msgbox will appear stating that “Freeze Panes” is on – and the file is not saved.

Indeed the file will not save until Freeze Panes is removed.