Freeze Panes Using VBA

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

Freeze Rows

Freeze Columns

Freeze Rows and Columns

UnFreeze Panes

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:

So that the complete code now looks like:

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.

VBA Coding Made Easy

Stop searching for VBA code online. Download our free add-in to quickly insert any of the sample code found on our site into the Visual Basic Editor.

alt text

Stop searching for VBA code online. Try the Code VBA Add-in…

Free Download!

<<Return to VBA Examples

Did you find this VBA tutorial useful? Then share it with your friends and colleagues: