Protecting Multiple Worksheets

S

Sam A

Hi
I need to protect multiple worksheets to lock cells. Only
way I can see to do this is to go into individual sheets
and do this. The 'protect sheet' option is greyed out
when multiple sheets are open.
Is it therefore possible to run a macro to protect /
unprotect worksheets with a password?
Thanking you in advance. Sam
 
R

Ron de Bruin

Try this Sam

Sub prot()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
sh.Protect "ron"
Next sh
Application.ScreenUpdating = True
End Sub

Sub unprot()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
sh.Unprotect "ron"
Next sh
Application.ScreenUpdating = True
End Sub
 
G

Guest

Ron!

Thank you - this worked perfectly - you little star!
Quick question - does sh.protect "ron" set the password?
Thanks
Sam
-----Original Message-----
Try this Sam

Sub prot()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
sh.Protect "ron"
Next sh
Application.ScreenUpdating = True
End Sub

Sub unprot()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
sh.Unprotect "ron"
Next sh
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




"Sam A" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
S

Sam A

Ron
Me again.
If the sh.protect "ron" is the password - is it possible
to make it look at a cell in the spreadsheet - otherwise
the password just runs and doesn't need to be entered. It
would be good to have the person enter the password (once)
manually to make it more protected.
something like - sh.Unprotect = sh.name cell ##

Does that make sense?

Thanks again - Sam
-----Original Message-----
Try this Sam

Sub prot()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
sh.Protect "ron"
Next sh
Application.ScreenUpdating = True
End Sub

Sub unprot()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
sh.Unprotect "ron"
Next sh
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




"Sam A" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
R

Ron de Bruin

You can use this

sh.Protect ThisWorkbook.Sheets("Sheet1").Range("A1").Value
 
S

Sam A

Ron - this is perfect! Thank you so much - you are an
absolute star. I appreciate your help immensly.
Sam
-----Original Message-----
You can use this

sh.Protect ThisWorkbook.Sheets("Sheet1").Range("A1").Value


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




"Sam A" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Top