Multiple Worksheets - Protection


G

Guest

What is the most best way, with a macro, to Protect all worksheets in a
workbook using a popup asking for a password, and specify what functions a
users can use? And then to unlock it also, with a popup asking for the
password.

For example, I want to protect all the worksheets and have users be able to
"Select Unlocked Cells", "Select Locked Cells", and "Format Cells".

Also, to unprotect all the worksheets with a macro that pops up asking for
the password to unprotect all the sheets.

I hope that makes sense.
 
Ad

Advertisements

G

Guest

This should be close...

Sub ProtectAll()
Dim wks As Worksheet
Dim strPassword

strPassword = InputBox("Enter the password", "Password")

For Each wks In Worksheets
wks.EnableSelection = xlNoRestrictions
wks.Protect Password:=strPassword, _
Contents:=True, _
AllowFormattingCells:=True
Next wks
End Sub

Sub UnProtectAll()
Dim wks As Worksheet
Dim strPassword

strPassword = InputBox("Enter the password", "Password")

On Error Resume Next
For Each wks In Worksheets
wks.Unprotect Password:=strPassword
Next wks
On Error GoTo 0
End Sub
 
G

Guest

Thanks Jim.

Jim Thomlinson said:
This should be close...

Sub ProtectAll()
Dim wks As Worksheet
Dim strPassword

strPassword = InputBox("Enter the password", "Password")

For Each wks In Worksheets
wks.EnableSelection = xlNoRestrictions
wks.Protect Password:=strPassword, _
Contents:=True, _
AllowFormattingCells:=True
Next wks
End Sub

Sub UnProtectAll()
Dim wks As Worksheet
Dim strPassword

strPassword = InputBox("Enter the password", "Password")

On Error Resume Next
For Each wks In Worksheets
wks.Unprotect Password:=strPassword
Next wks
On Error GoTo 0
End Sub
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top