In this Article
Freeze Panes Using VBA
I recently posted on Freezing panes in Excel, here’s how you can do it using VBA:
Rows("1:1").Select ActiveWindow.FreezePanes = True
Range("A:A").Select ActiveWindow.FreezePanes = True
Freeze Rows and Columns
Range("B2").Select ActiveWindow.FreezePanes = True
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.
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!