Password Applied to All Worksheets Confrimation Prompt
 

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

--
sns
-----------------------------------------------------------------------
snsd's Profile: http://www.excelforum.com/member.php...fo&userid=1591
View this thread: http://www.excelforum.com/showthread.php?threadid=27782

Password Applied to All Worksheets Confrimation Prompt
 

Hi

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

Cheers
JulieD

"snsd" wrote in message
...

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

!!!- 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
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
Else 'added this line
myPwd2 = InputBox(prompt:="Please reenter your password.") 'added this line
End If

If myPwd < myPwd2 then 'added this IF statement
msgbox "Passwords didn't match. Please try again later."
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 Sub


--
snsd
------------------------------------------------------------------------
snsd's Profile:
http://www.excelforum.com/member.php...o&userid=15910
View this thread: http://www.excelforum.com/showthread...hreadid=277827

Password Applied to All Worksheets Confrimation Prompt
 

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




"snsd" wrote in message
...

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 - 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
Dim wks As Worksheet

myPwd = InputBox(prompt:="Please enter the password to protect all
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 Sub


--
snsd
------------------------------------------------------------------------
snsd's Profile:
http://www.excelforum.com/member.php...o&userid=15910
View this thread: http://www.excelforum.com/showthread...hreadid=277827