VBA for protecting worksheets

skydive

New Member
Joined
Mar 8, 2011
Messages
7
Excel 2003. I have a workbook w/14 worksheets. I want to protect all sheets at one time. I'm using the following code, but I want to change the password. What do I need to do via VBA to customize a password?

Code is:

Option Explicit

Sub ProtectAll()

Dim wSheet As Worksheet
Dim Pwd As String

Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
For Each wSheet In Worksheets
wSheet.Protect Password:=Pwd
Next wSheet

End Sub

Sub UnProtectAll()

Dim wSheet As Worksheet
Dim Pwd As String

Pwd = InputBox("Enter your password to unprotect all worksheets", "Password Input")
On Error Resume Next
For Each wSheet In Worksheets
wSheet.Unprotect Password:=Pwd
Next wSheet
If Err <> 0 Then
MsgBox "You have entered an incorect password. All worksheets could not " & _
"be unprotected.", vbCritical, "Incorect Password"
End If
On Error Goto 0

End Sub
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Hi

Do you mean that you want the password hard-coded rather than have the inputbox? If so, you should be able to use something like this:

Code:
Sub ProtectAll()

Dim wSheet As Worksheet
Dim Pwd As String

Pwd = "yourpassword"

For Each wSheet In Worksheets
wSheet.Protect Password:=Pwd
Next wSheet

End Sub
 

skydive

New Member
Joined
Mar 8, 2011
Messages
7
I don't mind using the original code in order to protect/unprotect all at once...I just wanted to change the current password to mine that's all. Your code looks a bit easier though. What about code for unprotecting sheets all at once?
 

Forum statistics

Threads
1,141,842
Messages
5,708,919
Members
421,598
Latest member
NewHere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top