macro to remove worksheet-level passwords

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

Hi David,

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

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


---
Regards,
Norman


"DavidH" <DavidH@discussions.microsoft.com> wrote in message
news:0EB6A6D2-D2BD-4FF6-950E-36C9BBCF76D7@microsoft.com...
> 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

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" wrote:

> Hi David,
>
> To remove the sheet level passwords, see JE McGimpsey at:
>
> http://www.mcgimpsey.com/excel/removepwords.html
>
>
> ---
> Regards,
> Norman
>
>
> "DavidH" <DavidH@discussions.microsoft.com> wrote in message
> news:0EB6A6D2-D2BD-4FF6-950E-36C9BBCF76D7@microsoft.com...
> > 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
>
>
>

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
'<<=============


---
Regards,
Norman


"DavidH" <DavidH@discussions.microsoft.com> wrote in message
news:A944996F-324B-4748-A5B3-BECEB085306E@microsoft.com...
> 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" wrote:
>
>> Hi David,
>>
>> To remove the sheet level passwords, see JE McGimpsey at:
>>
>> http://www.mcgimpsey.com/excel/removepwords.html
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>> "DavidH" <DavidH@discussions.microsoft.com> wrote in message
>> news:0EB6A6D2-D2BD-4FF6-950E-36C9BBCF76D7@microsoft.com...
>> > 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
>>
>>
>>

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
'<<=============


---
Regards,
Norman



"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:u6i9LX%23GGHA.3752@TK2MSFTNGP11.phx.gbl...
> 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
> '<<=============
>
>
> ---
> Regards,
> Norman
>
>
> "DavidH" <DavidH@discussions.microsoft.com> wrote in message
> news:A944996F-324B-4748-A5B3-BECEB085306E@microsoft.com...
>> 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" wrote:
>>
>>> Hi David,
>>>
>>> To remove the sheet level passwords, see JE McGimpsey at:
>>>
>>> http://www.mcgimpsey.com/excel/removepwords.html
>>>
>>>
>>> ---
>>> Regards,
>>> Norman
>>>
>>>
>>> "DavidH" <DavidH@discussions.microsoft.com> wrote in message
>>> news:0EB6A6D2-D2BD-4FF6-950E-36C9BBCF76D7@microsoft.com...
>>> > 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,

Thanks so much, your code worked perfectly.

"Norman Jones" wrote:

> 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
> '<<=============
>
>
> ---
> Regards,
> Norman
>
>
>
> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
> news:u6i9LX%23GGHA.3752@TK2MSFTNGP11.phx.gbl...
> > 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
> > '<<=============
> >
> >
> > ---
> > Regards,
> > Norman
> >
> >
> > "DavidH" <DavidH@discussions.microsoft.com> wrote in message
> > news:A944996F-324B-4748-A5B3-BECEB085306E@microsoft.com...
> >> 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" wrote:
> >>
> >>> Hi David,
> >>>
> >>> To remove the sheet level passwords, see JE McGimpsey at:
> >>>
> >>> http://www.mcgimpsey.com/excel/removepwords.html
> >>>
> >>>
> >>> ---
> >>> Regards,
> >>> Norman
> >>>
> >>>
> >>> "DavidH" <DavidH@discussions.microsoft.com> wrote in message
> >>> news:0EB6A6D2-D2BD-4FF6-950E-36C9BBCF76D7@microsoft.com...
> >>> > 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
> >>>
> >>>
> >>>
> >
> >
>
>
>

All I want to say is: THANK YOU!!!