Multiple passwords for different users. Access restricted to certain worksheet

Hi Folks,

I hoping you can help me. I'm looking to allow access to colleagues to a spreadsheet with multiple worksheets. Each colleague would have their own tab that may contain personal information about them and also a summary tab with certain information on all employees that isn't sensitive.

I would like people to have to login to the workbook and depending on the user and password get access to their worksheet and view a summary sheet. I can't use the sheet I need to as it holds personal data on it but I have attached an alternative. It's a holiday chart with the same premise. Each user has their own tab and the information is collated into an overall holiday view so the principle is the same.

I've been doing some research and viewed a number of posts where a user list would be maintained in another tab that would only be viewable to the Admin user. VBA code would unlock/lock the various tabs depending on which user accesses the workbook.

If someone was able to help advise me on what code to use and how to install it I would be grateful.

Thanks

I have a similar set up to this (in a workbook I don't have access to right at this moment) but rather than messing around with passwords, it's all based on network username.
A workbook open event determines the username and displays the relevant sheet for that user. This is based on a list maintained by an Admin user.

Sheets that are not relevant to that user are hidden away using VeryHidden so a user cannot easily just unhide them.

Be wary though, Excel is not massively secure. Anyone with a little skill, the desire to poke around where they shouldn't and a spare 5 minutes could strip password protection from a workbook and access the VBA enabling them to unhide sheets.

To test if this is possible for you, press Alt+F11 to open the VB Editor, press Ctrl+G to open the Immediate Window (this usually appears at the bottom of the VBE). In that window type msgbox environ$("username") and press return. A box should show up and reveal your login ID.

BSB

Yes, that does come up with my Login ID

Good catch. I missed that because my system at work doesn't ask before enabling macros.

The below also goes in the code window for "ThisWorkbook"

BSB

Please Login or Register  to view this content.

Hey, thanks for your help guys.

However when I run the following code I get the error:

Compile Error:
Block If without End IF

Please Login or Register  to view this content.

Sorry, my bad! Told you it wasn't tested.

Move the end if line to after next I and before application.screenupdating = true.

BSB

Thanks

Now I get a Next without For compile error

Could you post your current code and show which line it errors on?

BSB

Thanks,

It errors on the final Next i just after Sheets(i).Visible = True

Please Login or Register  to view this content.

Rather than moving that End If you needed to add another End If. Didn't spot that as I was replying from my phone.
Try the below code:

BSB

Please Login or Register  to view this content.