Freeze Panes

[sjm cpa]

When I protect a worksheet in Excel 2007, the freeze pane option is not
available (grayed out). How can I make the option available without removing
worksheet protection?

 

[Gord Dibben]

I don't use 2007 but in 2003 you can freeze/unfreeze panes on a protected
worksheet.

You cannot freeze panes on a protected workbook.

If that is the case then use this macro.

Sub freeze()
ActiveWorkbook.Unprotect Password:="justme"
ActiveWindow.FreezePanes = False
Set srng = Application.InputBox(prompt:= _
"Select a cell", Type:=8)
srng.Select
ActiveWindow.FreezePanes = True
ActiveWorkbook.Protect Password:="justme", _
Structure:=True, Windows:=True
End Sub


Gord Dibben MS Excel MVP

 

[sjm cpa]

The workbook is not protected, just the worksheet page. From what I can
tell, this is new for 2007. Unprotecting the worksheet is not an option.

 

[Rick Rothstein]

From the bottom of this webpage...

http://office.microsoft.com/en-us/excel/HP012170481033.aspx

"The Freeze Panes command is not available when
you are in cell editing mode or when a worksheet
is protected."

So I am guessing this is a new "feature" of XL2007.<g>

 

[Gord Dibben]

Good to know.

Thanks Rick

 

[Gord Dibben]

Maybe you don't understand what the macro does?

The code will unprotect only to allow the cell selection for Freeze Panes
then re-protect.

So it can be an option for that one operation.


Gord

 

[sjm cpa]

I am trying it out today. Thanks so much for your help, I haven't done macros
in a while.

 

[sjm cpa]

The macro works to allow the freeze panes. How does it undo the freeze panes
after the entries are completed?

 

[Gord Dibben]

Which entries would those be?

Just run another macro if you want to unfreeze panes after you're done with
whatever you're doing.

Sub un_freeze()
ActiveWorkbook.Unprotect Password:="justme"
ActiveWindow.FreezePanes = False
ActiveWorkbook.Protect Password:="justme", _
Structure:=True, Windows:=True
End Sub


Gord