[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", _
Structu=True, Windows:=True
End Sub


Gord Dibben MS Excel MVP

On Thu, 11 Sep 2008 10:10:01 -0700, sjm cpa
wrote:

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?

[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.

"Gord Dibben" wrote:

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", _
Structu=True, Windows:=True
End Sub


Gord Dibben MS Excel MVP

On Thu, 11 Sep 2008 10:10:01 -0700, sjm cpa
wrote:

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?

[Rick Rothstein]

From the bottom of this webpage...

http://office.microsoft.com/en-us/ex...170481033.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

--
Rick (MVP - Excel)


"sjm cpa" wrote in message
...
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.

"Gord Dibben" wrote:

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", _
Structu=True, Windows:=True
End Sub


Gord Dibben MS Excel MVP

On Thu, 11 Sep 2008 10:10:01 -0700, sjm cpa
wrote:

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]

Good to know.

Thanks Rick

On Thu, 11 Sep 2008 15:46:10 -0400, "Rick Rothstein"
wrote:

From the bottom of this webpage...

http://office.microsoft.com/en-us/ex...170481033.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]

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

On Thu, 11 Sep 2008 12:26:01 -0700, sjm cpa
wrote:

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.

"Gord Dibben" wrote:

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", _
Structu=True, Windows:=True
End Sub


Gord Dibben MS Excel MVP

On Thu, 11 Sep 2008 10:10:01 -0700, sjm cpa
wrote:

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?

[sjm cpa]

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

"Gord Dibben" wrote:

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

On Thu, 11 Sep 2008 12:26:01 -0700, sjm cpa
wrote:

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.

"Gord Dibben" wrote:

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", _
Structu=True, Windows:=True
End Sub


Gord Dibben MS Excel MVP

On Thu, 11 Sep 2008 10:10:01 -0700, sjm cpa
wrote:

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?

[sjm cpa]

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

"Gord Dibben" wrote:

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", _
Structu=True, Windows:=True
End Sub


Gord Dibben MS Excel MVP

On Thu, 11 Sep 2008 10:10:01 -0700, sjm cpa
wrote:

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]

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", _
Structu=True, Windows:=True
End Sub


Gord

On Fri, 12 Sep 2008 08:00:00 -0700, sjm cpa
wrote:

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

"Gord Dibben" wrote:

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", _
Structu=True, Windows:=True
End Sub


Gord Dibben MS Excel MVP

On Thu, 11 Sep 2008 10:10:01 -0700, sjm cpa
wrote:

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?