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:

freeze panes

2. This will bring up:

freeze panes

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

freeze panes

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:

freeze panes

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.

freeze panes

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

AutoMacro - VBA Code Generator

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

alt text

Learn More!


<<Return to VBA Examples

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)