Password Applied to All Worksheets Confrimation Prompt

  • Thread starter Thread starter snsd
  • Start date Start date
S

snsd

Hi:

I found the following macro on this site which allows you to place
password on every sheet in a workbook. It works great. The only thing
would like to add is a second prompt to confirm the password - the sam
way that you are prompted to verify the password when you select Tool
-> Protection -> Protect Worksheet. I don't want to accidentally appl
an incorrect password to all my sheets and then forget it! (Note:
obviously only want to have to confirm it once - and not for ever
sheet!)

Thanks in advance for your help.


Sub PasswordAppliedToAllSheets()
Dim myPwd As String
Dim wks As Worksheet

myPwd = InputBox(prompt:="Please enter the password to protect al
sheets.")

If Trim(myPwd) = "" Then
Exit Sub
End If

For Each wks In ThisWorkbook.Worksheets
If wks.ProtectContents _
Or wks.ProtectDrawingObjects _
Or wks.ProtectScenarios Then
'already protected
Else
wks.Protect Password:=myPwd
End If
Next wks

End Su
 
Hi

i've put the changes in line with your original post

Cheers
JulieD

snsd said:
Hi:

I found the following macro on this site which allows you to place a
password on every sheet in a workbook. It works great. The only thing I
would like to add is a second prompt to confirm the password
Click to expand...

!!!- good idea!
- the same
way that you are prompted to verify the password when you select Tools
-> Protection -> Protect Worksheet. I don't want to accidentally apply
an incorrect password to all my sheets and then forget it! (Note: I
obviously only want to have to confirm it once - and not for every
sheet!)

Thanks in advance for your help.


Sub PasswordAppliedToAllSheets()
Dim myPwd As String
Click to expand...
Dim myPwd2 As String 'added this line
Dim wks As Worksheet

myPwd = InputBox(prompt:="Please enter the password to protect all
sheets.")

If Trim(myPwd) = "" Then
Exit Sub
Click to expand...
Else 'added this line
myPwd2 = InputBox(prompt:="Please reenter your password.") 'added this line
End If
Click to expand...
If myPwd <> myPwd2 then 'added this IF statement
msgbox "Passwords didn't match. Please try again later."
exit sub
End if
 
Why not hard code the password into the sub

Sub PasswordAppliedToAllSheets()
Dim myPwd As String
Dim wks As Worksheet

myPwd = "Password"
For Each wks In ThisWorkbook.Worksheets
'Rest of code

Regards
neil
 
Back
Top