Password Protection for Worksheets already protected

[Kristi]

I have a shared document that needs to be set up so that individuals can
access only the worksheet for them (no others) as well as protected cells
within that worksheet. I have set the passwords for the sheets as far as the
data is concerned, but is it possible to protect each worksheet with an
additional password or can only one password be used per worksheet? The
trouble is that one individual could/would enter data on anothers worksheet.

 

[JLatham]

Each worksheet can have a unique password. All you have to do is assign that
password to the sheet. Of course you also have to remember what the password
is for each of the sheets.

As long as the people using the workbook are 'honest' and all you're
interested in doing is keeping users from accidentally altering someone
else's data or using the wrong sheet, then things are fine. But remember
that the encryption of the password for both workbook and worksheet
protection is very weak and easily cracked - so it's not much for providing
true 'security' type protection.

You could put code in each worksheet's _Activate() event to prompt for its
password, and if the proper password isn't given, leave the sheet locked up.
Another person posting a question about this kind of thing had such a deal
set up: when you activated a sheet, it first hid all of the columns on that
sheet and requested the password. If the proper password wasn't given, then
the columns remained hidden (and the sheet remained protected), but if the
proper password was given, then the columns were made visible and sheet
protection was removed. The sheet's _Deactivate() event handler was set up
to always put the sheet back into protected state with the columns hidden
again.

Here's a link to their initial posting that shows the code they used in the
two worksheet events:
http://www.microsoft.com/office/com...misc&mid=509614b1-4e94-49e6-b95f-4da09fdbbed5

 

[Kristi]

How/where do I insert the code on the separate worksheets? Can it be the
same password as the data locked password?

 

[Gord Dibben]

One password per sheet.

You can get around this either of two ways but I'll add the warning that Excel's
internal security is quite weak and passwords can be broken.

1. Have workbook_open code that checks to see who is the log-in user and open
just that sheet with all others veryhidden or a blank(dummy) sheet if users
disable macros.

2. Each user has his own password. The workbook opens with a blank(dummy) sheet
and an InputBox for user to enter his password which opens his sheet only.

No password to open the workbook, just code to make a user's sheet visible.

In the Thisworkbook Module....................

Private Sub Workbook_Open()
Dim pword As String
Select Case Environ("Username")
'if a login is not used and each user has his own sheet password change to
'pword = InputBox("Enter Your Password")
'Select Case pword
Case Is = "Gord": Sheets("Gordsheet").Visible = True
Sheets("Dummy").Visible = False
Case Is = "Pete": Sheets("Petesheet").Visible = True
Sheets("Dummy").Visible = False

End Select
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Dummy").Visible = xlSheetVisible
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> "Dummy" Then
sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub

You would protect the project so's prying eyes cannot see the code and
passwords.

You may also want as administrator to be able to open all sheets for editing.

Add these lines to the Workbook_Open code above End Select

Case Is = "Admin"
For n = 1 To Sheets.Count
Sheets(n).Visible = True
Next n


Gord Dibben MS Excel MVP

 

[JLatham]

To put code in a worksheet's event handlers, right-click on the worksheet's
name tab and choose View Code from the popup list. You can choose Worksheet
from the left hand pull down at the top, and which event from the right hand
pull down list. You'll have a 'stub' for _SelectionChange() which you can
delete. In the case of the code I pointed to, you wouldn't need the
pull-downs, just copy and paste the code into that module. Note that you
have to do this for each worksheet, and of course modify the code to use the
appropriate password for it.

To get to where you need to be for the solution that Gord has offered,
right-click on the Excel icon immediately to the left of the word "File" in
the main Excel menu toolbar and again, choose [View Code] from that popup
list and copy/paste his code if you choose to go that route.

 

[Kristi]

I don't know how/where to enter these codes on the worksheets. I think this
would work, but I don't understand how to do it.

 

[Kristi]

Thank you. I'll give it a shot and hopefully get this thing working!

To put code in a worksheet's event handlers, right-click on the worksheet's
name tab and choose View Code from the popup list. You can choose Worksheet
from the left hand pull down at the top, and which event from the right hand
pull down list. You'll have a 'stub' for _SelectionChange() which you can
delete. In the case of the code I pointed to, you wouldn't need the
pull-downs, just copy and paste the code into that module. Note that you
have to do this for each worksheet, and of course modify the code to use the
appropriate password for it.

To get to where you need to be for the solution that Gord has offered,
right-click on the Excel icon immediately to the left of the word "File" in
the main Excel menu toolbar and again, choose [View Code] from that popup
list and copy/paste his code if you choose to go that route.

How/where do I insert the code on the separate worksheets? Can it be the
same password as the data locked password?

 

[Kristi]

Nothing happened. I'm sure I am not doing something right, I just don't know
what.

To put code in a worksheet's event handlers, right-click on the worksheet's
name tab and choose View Code from the popup list. You can choose Worksheet
from the left hand pull down at the top, and which event from the right hand
pull down list. You'll have a 'stub' for _SelectionChange() which you can
delete. In the case of the code I pointed to, you wouldn't need the
pull-downs, just copy and paste the code into that module. Note that you
have to do this for each worksheet, and of course modify the code to use the
appropriate password for it.

To get to where you need to be for the solution that Gord has offered,
right-click on the Excel icon immediately to the left of the word "File" in
the main Excel menu toolbar and again, choose [View Code] from that popup
list and copy/paste his code if you choose to go that route.

How/where do I insert the code on the separate worksheets? Can it be the
same password as the data locked password?

 

[Gord Dibben]

The two sets of code I posted below will go into the Thisworkbook module.

Jerry's second post explained how to do that.

Which way would you like to go? Jerry's method or mine?

If mine..............

Log-in username with a single password for all sheets which only you know or a
separate password for each sheet which only the user knows?

Send me a sample workbook by email with a list of some usernames or sheet
passwords and I'll see what I can come up with.

Change the AT and DOT for my email address.


Gord

 

[JLatham]

Kristi,
I was going to offer to have you send workbook to me to look over, but Gord
has beaten me to that offer, and I think to keep confusion to you to a
minimum it's probably best to work with just one mentor - in this case, I'll
defer to Gord, so go ahead and get in touch with him. I can't imagine either
of you needing me from this point, even though he doesn't sell insurance,
"You're in good hands with Gord" <g>, but if you do, just reply to this and
I'll be back.

Nothing happened. I'm sure I am not doing something right, I just don't know
what.

To put code in a worksheet's event handlers, right-click on the worksheet's
name tab and choose View Code from the popup list. You can choose Worksheet
from the left hand pull down at the top, and which event from the right hand
pull down list. You'll have a 'stub' for _SelectionChange() which you can
delete. In the case of the code I pointed to, you wouldn't need the
pull-downs, just copy and paste the code into that module. Note that you
have to do this for each worksheet, and of course modify the code to use the
appropriate password for it.

To get to where you need to be for the solution that Gord has offered,
right-click on the Excel icon immediately to the left of the word "File" in
the main Excel menu toolbar and again, choose [View Code] from that popup
list and copy/paste his code if you choose to go that route.

How/where do I insert the code on the separate worksheets? Can it be the
same password as the data locked password?

:

Each worksheet can have a unique password. All you have to do is assign that
password to the sheet. Of course you also have to remember what the password
is for each of the sheets.

As long as the people using the workbook are 'honest' and all you're
interested in doing is keeping users from accidentally altering someone
else's data or using the wrong sheet, then things are fine. But remember
that the encryption of the password for both workbook and worksheet
protection is very weak and easily cracked - so it's not much for providing
true 'security' type protection.

You could put code in each worksheet's _Activate() event to prompt for its
password, and if the proper password isn't given, leave the sheet locked up.
Another person posting a question about this kind of thing had such a deal
set up: when you activated a sheet, it first hid all of the columns on that
sheet and requested the password. If the proper password wasn't given, then
the columns remained hidden (and the sheet remained protected), but if the
proper password was given, then the columns were made visible and sheet
protection was removed. The sheet's _Deactivate() event handler was set up
to always put the sheet back into protected state with the columns hidden
again.

Here's a link to their initial posting that shows the code they used in the
two worksheet events:
http://www.microsoft.com/office/com...misc&mid=509614b1-4e94-49e6-b95f-4da09fdbbed5



:

I have a shared document that needs to be set up so that individuals can
access only the worksheet for them (no others) as well as protected cells
within that worksheet. I have set the passwords for the sheets as far as the
data is concerned, but is it possible to protect each worksheet with an
additional password or can only one password be used per worksheet? The
trouble is that one individual could/would enter data on anothers worksheet.

 

[Gord Dibben]

Thanks Jerry

We may need you so don't stray too far<g>


Gord

Kristi,
I was going to offer to have you send workbook to me to look over, but Gord
has beaten me to that offer, and I think to keep confusion to you to a
minimum it's probably best to work with just one mentor - in this case, I'll
defer to Gord, so go ahead and get in touch with him. I can't imagine either
of you needing me from this point, even though he doesn't sell insurance,
"You're in good hands with Gord" <g>, but if you do, just reply to this and
I'll be back.

Nothing happened. I'm sure I am not doing something right, I just don't know
what.

To put code in a worksheet's event handlers, right-click on the worksheet's
name tab and choose View Code from the popup list. You can choose Worksheet
from the left hand pull down at the top, and which event from the right hand
pull down list. You'll have a 'stub' for _SelectionChange() which you can
delete. In the case of the code I pointed to, you wouldn't need the
pull-downs, just copy and paste the code into that module. Note that you
have to do this for each worksheet, and of course modify the code to use the
appropriate password for it.

To get to where you need to be for the solution that Gord has offered,
right-click on the Excel icon immediately to the left of the word "File" in
the main Excel menu toolbar and again, choose [View Code] from that popup
list and copy/paste his code if you choose to go that route.

:

How/where do I insert the code on the separate worksheets? Can it be the
same password as the data locked password?

:

Each worksheet can have a unique password. All you have to do is assign that
password to the sheet. Of course you also have to remember what the password
is for each of the sheets.

As long as the people using the workbook are 'honest' and all you're
interested in doing is keeping users from accidentally altering someone
else's data or using the wrong sheet, then things are fine. But remember
that the encryption of the password for both workbook and worksheet
protection is very weak and easily cracked - so it's not much for providing
true 'security' type protection.

You could put code in each worksheet's _Activate() event to prompt for its
password, and if the proper password isn't given, leave the sheet locked up.
Another person posting a question about this kind of thing had such a deal
set up: when you activated a sheet, it first hid all of the columns on that
sheet and requested the password. If the proper password wasn't given, then
the columns remained hidden (and the sheet remained protected), but if the
proper password was given, then the columns were made visible and sheet
protection was removed. The sheet's _Deactivate() event handler was set up
to always put the sheet back into protected state with the columns hidden
again.

Here's a link to their initial posting that shows the code they used in the
two worksheet events:
http://www.microsoft.com/office/com...misc&mid=509614b1-4e94-49e6-b95f-4da09fdbbed5



:

I have a shared document that needs to be set up so that individuals can
access only the worksheet for them (no others) as well as protected cells
within that worksheet. I have set the passwords for the sheets as far as the
data is concerned, but is it possible to protect each worksheet with an
additional password or can only one password be used per worksheet? The
trouble is that one individual could/would enter data on anothers worksheet.

 

[JLatham]

Posts to this thread should send me a notice, and if not, email to
HelpFrom @ jlathamsite. com without the spaces will always get my attention.

Thanks Jerry

We may need you so don't stray too far<g>


Gord

Kristi,
I was going to offer to have you send workbook to me to look over, but Gord
has beaten me to that offer, and I think to keep confusion to you to a
minimum it's probably best to work with just one mentor - in this case, I'll
defer to Gord, so go ahead and get in touch with him. I can't imagine either
of you needing me from this point, even though he doesn't sell insurance,
"You're in good hands with Gord" <g>, but if you do, just reply to this and
I'll be back.

Nothing happened. I'm sure I am not doing something right, I just don't know
what.

:

To put code in a worksheet's event handlers, right-click on the worksheet's
name tab and choose View Code from the popup list. You can choose Worksheet
from the left hand pull down at the top, and which event from the right hand
pull down list. You'll have a 'stub' for _SelectionChange() which you can
delete. In the case of the code I pointed to, you wouldn't need the
pull-downs, just copy and paste the code into that module. Note that you
have to do this for each worksheet, and of course modify the code to use the
appropriate password for it.

To get to where you need to be for the solution that Gord has offered,
right-click on the Excel icon immediately to the left of the word "File" in
the main Excel menu toolbar and again, choose [View Code] from that popup
list and copy/paste his code if you choose to go that route.

:

How/where do I insert the code on the separate worksheets? Can it be the
same password as the data locked password?

:

Each worksheet can have a unique password. All you have to do is assign that
password to the sheet. Of course you also have to remember what the password
is for each of the sheets.

As long as the people using the workbook are 'honest' and all you're
interested in doing is keeping users from accidentally altering someone
else's data or using the wrong sheet, then things are fine. But remember
that the encryption of the password for both workbook and worksheet
protection is very weak and easily cracked - so it's not much for providing
true 'security' type protection.

You could put code in each worksheet's _Activate() event to prompt for its
password, and if the proper password isn't given, leave the sheet locked up.
Another person posting a question about this kind of thing had such a deal
set up: when you activated a sheet, it first hid all of the columns on that
sheet and requested the password. If the proper password wasn't given, then
the columns remained hidden (and the sheet remained protected), but if the
proper password was given, then the columns were made visible and sheet
protection was removed. The sheet's _Deactivate() event handler was set up
to always put the sheet back into protected state with the columns hidden
again.

Here's a link to their initial posting that shows the code they used in the
two worksheet events:
http://www.microsoft.com/office/com...misc&mid=509614b1-4e94-49e6-b95f-4da09fdbbed5



:

I have a shared document that needs to be set up so that individuals can
access only the worksheet for them (no others) as well as protected cells
within that worksheet. I have set the passwords for the sheets as far as the
data is concerned, but is it possible to protect each worksheet with an
additional password or can only one password be used per worksheet? The
trouble is that one individual could/would enter data on anothers worksheet.

 

[JLatham]

I think the method you've provided here, using the Workbook events is a good
way to go. It permits Kristi to have true password protection on the
individual worksheets while the individual user's password/username is acting
as a pseudo-password to make the sheets they're permitted to work with
visible to them. Only one small reminder to mention, and that's Excel's
requirement that at least one worksheet always be visible, and it looks to me
as if the Dummy sheet you've referenced is there for exactly that purpose.

 

[Gord Dibben]

I use the dummy sheet for two reasons.

1. You must have at least one sheet as you mention.

2. Kristi can leave a 36 pt. message in the middle of this sheet stating "You
have disabled macros, rendering this workbook unusable. Reopen with macros
enabled"


Gord

 

[JLatham]

I was going to suggest something like that - and then figured you'd take
advantage of the 'opportunity' on your own. I've done same thing in a couple
of projects I've done - even including instructions on how to set Macro
Security levels on the sheet, just in case.

 

[Gord Dibben]

Jerry

An update on this............Kristi mailed me the workbook and we got it set up
using the method of hiding sheets except for "Dummy" and user enters a password
to unhide just the user sheet.

Kristi was pleased with this.


Gord

Thanks Jerry

We may need you so don't stray too far<g>


Gord

Kristi,
I was going to offer to have you send workbook to me to look over, but Gord
has beaten me to that offer, and I think to keep confusion to you to a
minimum it's probably best to work with just one mentor - in this case, I'll
defer to Gord, so go ahead and get in touch with him. I can't imagine either
of you needing me from this point, even though he doesn't sell insurance,
"You're in good hands with Gord" <g>, but if you do, just reply to this and
I'll be back.

Nothing happened. I'm sure I am not doing something right, I just don't know
what.

:

To put code in a worksheet's event handlers, right-click on the worksheet's
name tab and choose View Code from the popup list. You can choose Worksheet
from the left hand pull down at the top, and which event from the right hand
pull down list. You'll have a 'stub' for _SelectionChange() which you can
delete. In the case of the code I pointed to, you wouldn't need the
pull-downs, just copy and paste the code into that module. Note that you
have to do this for each worksheet, and of course modify the code to use the
appropriate password for it.

To get to where you need to be for the solution that Gord has offered,
right-click on the Excel icon immediately to the left of the word "File" in
the main Excel menu toolbar and again, choose [View Code] from that popup
list and copy/paste his code if you choose to go that route.

:

How/where do I insert the code on the separate worksheets? Can it be the
same password as the data locked password?

:

Each worksheet can have a unique password. All you have to do is assign that
password to the sheet. Of course you also have to remember what the password
is for each of the sheets.

As long as the people using the workbook are 'honest' and all you're
interested in doing is keeping users from accidentally altering someone
else's data or using the wrong sheet, then things are fine. But remember
that the encryption of the password for both workbook and worksheet
protection is very weak and easily cracked - so it's not much for providing
true 'security' type protection.

You could put code in each worksheet's _Activate() event to prompt for its
password, and if the proper password isn't given, leave the sheet locked up.
Another person posting a question about this kind of thing had such a deal
set up: when you activated a sheet, it first hid all of the columns on that
sheet and requested the password. If the proper password wasn't given, then
the columns remained hidden (and the sheet remained protected), but if the
proper password was given, then the columns were made visible and sheet
protection was removed. The sheet's _Deactivate() event handler was set up
to always put the sheet back into protected state with the columns hidden
again.

Here's a link to their initial posting that shows the code they used in the
two worksheet events:
http://www.microsoft.com/office/com...misc&mid=509614b1-4e94-49e6-b95f-4da09fdbbed5



:

I have a shared document that needs to be set up so that individuals can
access only the worksheet for them (no others) as well as protected cells
within that worksheet. I have set the passwords for the sheets as far as the
data is concerned, but is it possible to protect each worksheet with an
additional password or can only one password be used per worksheet? The
trouble is that one individual could/would enter data on anothers worksheet.

 

[JLatham]

That's good news - I knew you'd fix her up with a good solution.

Jerry

An update on this............Kristi mailed me the workbook and we got it set up
using the method of hiding sheets except for "Dummy" and user enters a password
to unhide just the user sheet.

Kristi was pleased with this.


Gord

Thanks Jerry

We may need you so don't stray too far<g>


Gord

Kristi,
I was going to offer to have you send workbook to me to look over, but Gord
has beaten me to that offer, and I think to keep confusion to you to a
minimum it's probably best to work with just one mentor - in this case, I'll
defer to Gord, so go ahead and get in touch with him. I can't imagine either
of you needing me from this point, even though he doesn't sell insurance,
"You're in good hands with Gord" <g>, but if you do, just reply to this and
I'll be back.

:

Nothing happened. I'm sure I am not doing something right, I just don't know
what.

:

To put code in a worksheet's event handlers, right-click on the worksheet's
name tab and choose View Code from the popup list. You can choose Worksheet
from the left hand pull down at the top, and which event from the right hand
pull down list. You'll have a 'stub' for _SelectionChange() which you can
delete. In the case of the code I pointed to, you wouldn't need the
pull-downs, just copy and paste the code into that module. Note that you
have to do this for each worksheet, and of course modify the code to use the
appropriate password for it.

To get to where you need to be for the solution that Gord has offered,
right-click on the Excel icon immediately to the left of the word "File" in
the main Excel menu toolbar and again, choose [View Code] from that popup
list and copy/paste his code if you choose to go that route.

:

How/where do I insert the code on the separate worksheets? Can it be the
same password as the data locked password?

:

Each worksheet can have a unique password. All you have to do is assign that
password to the sheet. Of course you also have to remember what the password
is for each of the sheets.

As long as the people using the workbook are 'honest' and all you're
interested in doing is keeping users from accidentally altering someone
else's data or using the wrong sheet, then things are fine. But remember
that the encryption of the password for both workbook and worksheet
protection is very weak and easily cracked - so it's not much for providing
true 'security' type protection.

You could put code in each worksheet's _Activate() event to prompt for its
password, and if the proper password isn't given, leave the sheet locked up.
Another person posting a question about this kind of thing had such a deal
set up: when you activated a sheet, it first hid all of the columns on that
sheet and requested the password. If the proper password wasn't given, then
the columns remained hidden (and the sheet remained protected), but if the
proper password was given, then the columns were made visible and sheet
protection was removed. The sheet's _Deactivate() event handler was set up
to always put the sheet back into protected state with the columns hidden
again.

Here's a link to their initial posting that shows the code they used in the
two worksheet events:
http://www.microsoft.com/office/com...misc&mid=509614b1-4e94-49e6-b95f-4da09fdbbed5



:

I have a shared document that needs to be set up so that individuals can
access only the worksheet for them (no others) as well as protected cells
within that worksheet. I have set the passwords for the sheets as far as the
data is concerned, but is it possible to protect each worksheet with an
additional password or can only one password be used per worksheet? The
trouble is that one individual could/would enter data on anothers worksheet.