Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
msquery prompt for password | Excel Discussion (Misc queries) | |||
Password Prompt | Excel Discussion (Misc queries) | |||
Password Prompt | Excel Discussion (Misc queries) | |||
DSN UserId and Password Prompt | Excel Programming | |||
DSN UserId and Password Prompt | Excel Programming |