If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
User controlled Freeze Pane while protected
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 |
#2
|
|||
|
|||
User controlled Freeze Pane while protected
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", Structu=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 On Tue, 9 Feb 2010 14:33:02 -0800, FredL wrote: 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 |
#3
|
|||
|
|||
User controlled Freeze Pane while protected
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" wrote: 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", Structu=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 On Tue, 9 Feb 2010 14:33:02 -0800, FredL wrote: 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 . |
#4
|
|||
|
|||
User controlled Freeze Pane while protected
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 On Wed, 10 Feb 2010 10:28:05 -0800, FredL wrote: 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" wrote: 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", Structu=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 On Tue, 9 Feb 2010 14:33:02 -0800, FredL wrote: 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 . |
Thread Tools | |
Display Modes | |
|
|