Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Unlock All Protected Worksheets
Good morning. A couple of weeks ago, someone from this forum, was very
helpful in my question on how to unprotect protected worksheets, with similar passwords, with a macro. Below is a copy of the VB script I was given. It worked but then I realized, the password "hi" is now visible under the macro script. Is there a way to rewrite the code so that it will not show the password and instead when the macro is run, one has to enter the password before the macro can unprotect the protected worksheets? thank you very much, Storm Option Explicit sub testme() dim myPWD as string dim wks as worksheet myPWD = "hi" for each wks in activeworkbook.worksheets wks.unprotect password:=mypwd next wks end sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Unlock All Protected Worksheets
Assuming that all the worksheets have the same password, you can use code
like Sub UnlockWorksheets() Dim PW As String Dim WS As Worksheet On Error GoTo ErrHandler: PW = Application.InputBox(prompt:="Enter Password", Type:=2) If StrPtr(PW) = 0 Then ' user cancelled Exit Sub End If For Each WS In Worksheets WS.Unprotect Password:=PW Next WS Exit Sub ErrHandler: MsgBox "Invalid Password", vbOKOnly End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Storm" wrote in message ... Good morning. A couple of weeks ago, someone from this forum, was very helpful in my question on how to unprotect protected worksheets, with similar passwords, with a macro. Below is a copy of the VB script I was given. It worked but then I realized, the password "hi" is now visible under the macro script. Is there a way to rewrite the code so that it will not show the password and instead when the macro is run, one has to enter the password before the macro can unprotect the protected worksheets? thank you very much, Storm Option Explicit sub testme() dim myPWD as string dim wks as worksheet myPWD = "hi" for each wks in activeworkbook.worksheets wks.unprotect password:=mypwd next wks end sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Unlock All Protected Worksheets
On Sep 17, 8:49 am, "Chip Pearson" wrote:
Assuming that all the worksheets have the same password, you can use code like Sub UnlockWorksheets() Dim PW As String Dim WS As Worksheet On Error GoTo ErrHandler: PW = Application.InputBox(prompt:="Enter Password", Type:=2) If StrPtr(PW) = 0 Then ' user cancelled Exit Sub End If For Each WS In Worksheets WS.Unprotect Password:=PW Next WS Exit Sub ErrHandler: MsgBox "Invalid Password", vbOKOnly End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consultingwww.cpearson.com (email on the web site) "Storm" wrote in message ... Good morning. A couple of weeks ago, someone from this forum, was very helpful in my question on how to unprotect protected worksheets, with similar passwords, with a macro. Below is a copy of the VB script I was given. It worked but then I realized, the password "hi" is now visible under the macro script. Is there a way to rewrite the code so that it will not show the password and instead when the macro is run, one has to enter the password before the macro can unprotect the protected worksheets? thank you very much, Storm Option Explicit sub testme() dim myPWD as string dim wks as worksheet myPWD = "hi" for each wks in activeworkbook.worksheets wks.unprotect password:=mypwd next wks end sub- Hide quoted text - - Show quoted text - And If we wanted to "re-lock" using the same password what would we add? Dennis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to unlock and lock cells in password protected sheet | Excel Discussion (Misc queries) | |||
How to Unlock Worksheets with a Macro? | Excel Worksheet Functions | |||
Unlock protected VBA | Excel Programming | |||
how do i password protect an .xls file? how do i unlock it for automation. e.g. want to unlock and access a .xls from another .xls macro. | Excel Worksheet Functions | |||
How do i unlock protected worksheets? | Excel Worksheet Functions |