macro to remove worksheet-level passwords

[Guest]

Hi,

Our workgroup recently standardized on a group password. The intent was for
group members to set the group password via tools/options/security, password
to modify, with the "read only recommended" box not checked. However, group
members are setting up passwords every which way, including setting
worksheet-level passwords. Can anyone suggest a macro routine that will open
the worksheet, remove all workhseet level passwords, and set the group
passowrd via tools/options security?

I know the group password, so I'm not looking for a password-cracking
routine; I just want something that will standardize the way we set up our
common password.

Thanks in advance for any help!

David

 

[Norman Jones]

Hi David,

To remove the sheet level passwords, see JE McGimpsey at:

http://www.mcgimpsey.com/excel/removepwords.html

 

[Guest]

Hi Norman,

Thanks. It appears that McGimpsey's code is a password cracker, which I
don't really need. I'm assuming that users will open the workbook, enter the
password manually, then run the macro to remove worksheet-level protection.

 

[Norman Jones]

Hi David,

Try:

'=============>>
Public Sub Tester2()
Dim SH As Worksheet
Const PWORD As String = "GROUP PASSWORD" '<<== CHANGE

For Each SH In ActiveWorkbook.Worksheets
SH.Unprotect Password:=PWORD
Next SH

End Sub
'<<=============

 

[Norman Jones]

Hi David,

Or, if you do not want to commit the password to code, try:

'=============>>
Public Sub Tester2A()
Dim SH As Worksheet
Dim PWORD As String

PWORD = InputBox("Insert password")

For Each SH In ActiveWorkbook.Worksheets
SH.Unprotect Password:=PWORD
Next SH

End Sub
'<<=============

 

[Guest]

Norman,

Thanks so much, your code worked perfectly.