password protected worksheet

[alpertheidiot]

hi,

I have an excel workbook with multiple worksheets, (A, B, C, D), and each one of them are password protected for privacy reasons. This workbook is kept on a public folder and User A uses his password to access his own worksheet, and user B uses his password to access his own worksheet and so on..

This seems to be working fine, but now I need to give access to an additional user for say, worksheet A (the same will be necessary for other worksheet too). So in other words, there will be two users accessing to the same worksheet, but they must have different passwords (their passwords are used elsewhere, so they cannot know each others' password).

How can I achieve that? The code I currently use is as below:

Please Login or Register  to view this content.

thanks

[JBeaucaire]

1) Create a PASSWORDSLIST sheet where you can store a worksheet password conversion table.

2) On the sheet are four columns, USER, PASSWORD, SHEET PASSWORD, SHEET NAME

Please Login or Register  to view this content.

3) Hide the sheet xlVeryHidden (can only be unhidden by VBA)

4) Now adapt your macros to take the user password input, but then find that password on the hidden sheet and "Convert" that into the correct password to open the sheet.

5) Call the Unlock macro with the new password

[ajryan88]

Hi,

There is no way to do it using the "Unprotect Worksheet" default dialog, but you could build a custom input box for the user to enter their password, and then compare the password entered to some a list of acceptable passwords, and then unprotect the sheet if the password enters one in the list.

Only suggestion with this though, is that I would then password protect the VBA so that each user can't see any other user password.

Hope this helps

[alpertheidiot]

hi,
thank you both for the comments.

JBeaucaire, I'm not a programmer, but I can make sense of codes to a certain degree, would it be possible for you to send a file as an example? from that point on I can modify the code bit by bit to change sheet names and passwords etc.

ajryan88, thank you for the sample file. the problem I have there is sheets are visible to each user, and they shouldn't be. Each user should be able to access to only his/her worksheet.

See example of the workbook I have, current passwords for each sheet are: aa, bb, cc, dd. All I need is now to add another user with different password, for example both aa and aaaa should give access to 2013-A worksheet.

Hope that makes sense, and thanks in advance for your comments.

Password protected sheets.xlsb

[alpertheidiot]

any thoughts anybody? a sample file would be much appreciated..
thanks

[ajryan88]

Hi,

I believe that instead of the "Unprotect" method, you want the hidden worksheet method. I have amended my code in the previous attachment to cater for this.

See how this goes

[JBeaucaire]

These aren't really password protected sheets, they're password "hidden" sheets. You can remove the "access password list" button later, if you wish, this is simply to give you ready access to the hidden sheet listing all the sheetnames and passwords given to users to access those sheets. The password for that sheet is pwd

[alpertheidiot]

your samples are great, in particular JBeaucaire's sample is precisely what I needed. Many thanks for the excellent support.

[JBeaucaire]

I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above to mark a thread as SOLVED. Thanks.

[alpertheidiot]

Will do, thank you.

[xlbaby]

I had a similar case and this thread actually helped me in resolving it.

However, I do have another question with regards to this. is there a way you can summarize all the date from all the worksheets into a single sheet?

For e.g.: If all the sheets within the workbook has some data (which gets updated every week) from A2 until D4, can we summarize all those information on a sheet named "consolidated figures" worksheet? Also, say the next week the data is entered in B2 until B4, the data on these cells should show up on the "consolidated figures". Can it be done?

Please help me.

[JBeaucaire]

This is a separate question, as per forum rules, post your questions in a new thread of your own.
Thanks.

[xlbaby]

Thanks JBeaucaire. I will post this is a new question. I apologize.