User controlled Freeze Pane while protected

[FredL]

Hi,

I have a worksheet where it is protected and locked.
Is there anyway to code there such that the user (who cannot unlock the
sheet) can still use the freeze pane function so that they can determine how
to view the worksheet? I know you can enable the grouping via macro (e.g.
..EnableOutlining) while it is protected. Can the same be done for freeze
pane?

Thanks,
Fred

 

[Gord Dibben]

Not clear what type of protection and locking you have.

Worksheet protection does not disable Freeze Panes.

As long as users can select unlocked or locked cells they can freeze panes
anywhere they want.

Do you mean workbook protection is set?

If that's the case you would have to remove workbook protection before
unfreezing and re-freezing then re-protect.

Sub freeze_it()
With ActiveWorkbook
.Unprotect Password:="justme"
With ActiveWindow
.FreezePanes = False
Set pickcell = Application.InputBox(prompt:= _
"Select A Cell", Type:=8)
pickcell.Select
.FreezePanes = True
End With
.Protect Password:="justme", Structure:=True, Windows:=True
End With
End Sub

Note: if users cannot select any cells due to sheet protection then you
would have to deal with also.


Gord Dibben MS Excel MVP

 

[FredL]

Bummer. The worksheet is protected and would like to keep it protected (to
protect the formulas and results), but would like the user be able to set the
freeze pane as they desire since they could be using any location in a rather
large worksheet (large in the sense of being able to see all the relevant
cells).

Thanks for the reply.

 

[Gord Dibben]

So you have ALL cells locked on this protected sheet?

Users can select no cells at all to set Freeze Panes?

What is the purpose of that?

I would select the cells I want locked and unlock all others then protect
the sheet.

Your formulas will be protected and users can select any other cell to
freeze panes.

If you think you need all cells locked why not just get rid of the sheet
protection and save the file as "read-only"?

Then users can muddle about all they want but cannot save the file.


Gord