Password protecting individual worksheets in excel so each person can only view and edit their own

Copper Contributor

Hi there,

I am looking for a way to protect individual worksheets within in a workbook so that an admin could view and edit all worksheets but staff could only view and edit the worksheet they are assigned to.  Is this possible?  Can you help with instructions or point me to a tutorial?

7 Replies
It looks as though it would require some coding, if you want to automatically hide and show individual sheets based on individual identities

I would probably keep it simple, adding a macro that would run when the spreadsheet was opened, look up the user's Windows login name, and use that to decide which sheet to open up. Each user's login name could be stored somewhere on their sheet, making it easy for macro to run through the sheets until it found the right login name. This is pretty easy to create.

VBa can make sheets "very hidden", too, meaning they won't even show up in the list of sheets from Excel menus.

Is that the kind of thing you want?

@Katie_V 

 

Worksheet protection is notoriously weak. Even someone with little skill can google the instructions to break it. But, if you still wanted to do it, then you would need to use VBA. You should be able to get the user id using the environ function.

 

And, you would need to set up an event handler in the ThisWorkbook VBA module of your workbook to protect all sheets when opened, then get the user id and unprotect their sheet(s). You would also need to run the protectsheet routine before saving and the unprotect routine after saving.

 

Also, you would need to be sure to use the worksheet code names, which are viewable in the VBA project viewer or it is in the (Name) field when you click on the properties button in the developer tab, so  that users can't circumvent your macro by changing the worksheet tab name. And, you would have to protect the VBA project so they can't easily get your passwords (but, this is also easy to break if one googles it).

 

If the data is sensitive and you cannot risk users intentionally getting to other worksheets, then you'll have to find another way as Excel's internal "security" is not secure if people make any effort to break it.

 

 I attached an example, sort of a rough draft, to illustrate.

That's pretty much what I suggested above.

I agree that Excel's security is weak. However, if the users aren't savvy and the data is not very sensitive, it is probably OK.

I believe, however, there is a really secure option, for extreme situations. The entire contents of each sheet could be AES encrypted in place using passwords unique to each page. The code would request a password and anything except the correct pass would result in gibberish.

Yes, I did not see your response until I refreshed the page.

If the user has to enter a password to decrypt a worksheet, then that seems to be as much work as having them enter a password to unprotect the sheet. And, an admin would have to enter passwords for all of the sheets. I'm assuming that is what the OP is trying to avoid, otherwise, I wouldn't see the point in asking?

@JMB17 
I suppose that is the problem with software that is designed to be open and collaborative  - it isn't good at being secretive!

I have used AES encryption in several cases over the years, even encrypting individual rows of data with distinct passwords, so that users could "log in" and decrypt and use their individual data without the need for an internet connection.


@Katie_V   As noted by previous commenters, Excel security is NOT secure! But I have done something similar to what you want for an internal document for training reports so employees can easily see their own reports but if they see others it isn't a big deal either.  I spent some time this evening initially 'scrubbing' my training matrix / report and then decided to just scrap it and write  a very simple PW sheet control.  In the attached is the VBA code to basically do what you want.  On open, all sheets except "Home" are hidden.  Click the button "Login" to gain access.  The admin is Lotus with PW of 123 (thought that was funny).  i also have the PW for the VBA set to 123 and the master sheet/workbook PW set to 123 (that is a constant in the VBA on the frmPassword form code).  After opening with the Admin account (Lotus 123) you can look at the "Main" tab to see how to add users, passwords and "R" or "RW" permissions for each page.  If the macro sees "R" or "RW" it makes the page visible and if it sees "RW" it also unlocks the page.

I really didn't intend to spend this much time on creating this for you/others but I did and it is well past my bedtime so I haven't tested everything, fixed everything, nor added all the features that would be nice. But don't worry, the bugs are included at no extra charge.

AGAIN, Excel is NOT secure and therefore I made no attempt to make this particularly secure.  You could use the "very hidden" and you could encrypt the passwords and much much more, but in the end it is still in excel and therefore inherently NOT particularly secure.

So I hope this might be of some help to you.


Apply different passwords or permissions to separate ranges in workbooks in Excel
https://docs.microsoft.com/en-gb/office/troubleshoot/excel/apply-password-to-separate-range

or with VBA
uNam is the login name in the network.
If the employee now logs on, their associated sheet is shown.

When the file is closed, the sheets are hidden and cannot be shown under Format - Sheet.

Private Sub Workbook_Open()
uNam = Environ("Username")
If uNam = "yyy" Then Sheets("Tabelle1").Visible = True
If uNam = "abc" Then Sheets("Tabelle2").Visible = True
End Sub



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Tabelle1").Visible = xlVeryHidden
Sheets("Tabelle2").Visible = xlVeryHidden
End Sub

Nikolino
I know I don't know anything (Socrates)