Making Pane Settings Persist

Here's something that may be helpful if Cindy's problem is caused by multiple people using the same workbook.

If Cindy creates a Private Sub Workbook_Open() macro that changes the workbook appearance every time the workbook is opened, she may inadvertently start an office war over the "proper" appearance of the workbook. This is even more likely if Cindy is not the main user.

How to fix this?

Simply include a test in the Workbook_Open() macro to determine whether Cindy is opening the workbook.

If it is Cindy, go ahead and do Cindy's formatting.

If not Cindy, then quit the macro.

The test looks something like:
   If Application.UserName <> "Cindy") Then Exit Sub

Replace "Cindy" with her actual User Name in Microsoft Office.

Instead of Exit Sub, the macro could take some other action. For example, if Cindy's workbook nemesis is someone higher up, Cindy could write the macro to provide that user with the specific layout they want. Result: everyone gets what they want and Cindy's boss is happy.

Christian,
I don't know of a way to freeze panes on a worksheet without selecting (activating) first. After all, FreezePanes is purely visual so there would be no reason to freeze the panes until it is to be viewed. Your comment about selecting Range("A1") is a good one, except you should select Range("A1") AFTER selecting (and freezing the panes) Range("D4") if you want the cursor to end up on A1. With your code, the cursor ends up on the last selected cell - D4 - and you'll still see the first 4 rows and columns in the active window.

Also note that Allen's code above in the Workbook_Open module --

Sheets("Sheet1").Range("D4").Select
ActiveWindow.FreezePanes = True

will only work if the workbook opens on Sheet1. As you know the workbook will open to whatever worksheet had the active window when last saved. So, if you were on Sheet2, saved and closed the file, then reopened it you would get a range select error code '1004'. You would need to separate the worksheet select and range select into two separate actions.

Sheets("Sheet1").Select
Range("D4").Select

If you want to freeze the panes of a worksheet during the Workbook_Open event without ending up on that worksheet, the following code will work. Basically, it sets the active worksheet during open to a variable (OpenSht), selects and freezes the panes for the desired worksheet (in this case Sheet2), then activates the original active worksheet. We use the Application.ScreenUpdating = False to perform this in the background.

Private Sub Workbook_Open()
Dim OpenSht As Worksheet

Application.ScreenUpdating = False

Set OpenSht = ActiveSheet

Sheets("Sheet2").Select
Range("D4").Select
ActiveWindow.FreezePanes = True
Range("A1").Select

OpenSht.Activate
Range("A1").Select

Application.ScreenUpdating = True
End Sub

Hope this helps.

I have a small comment on your solution: It may happen that the window is scrolled and - in your example - the row 4 and/or the column D is the first one currently displayed. If the real intent is to always show the first 4 rows and columns you should add an extra statement as shown below:

Sub SetPanes()
ActiveSheet.Range("A1").Select ' make sure the top cell is displayed
ActiveSheet.Range("D4").Select
ActiveWindow.FreezePanes = True
End Sub


By the way: is there an alternative of setting the freeze zone without activating the sheet?

Best regards,
Christian