Password protecting individual sheets in Excel

I've password protected each sheet in a file I'm currently working on but the code I found for it hides the sheet if the password is incorrect. That would be fine but this will be a shared file with roughly 25 users and each user only knows the password for their sheet. So if they accidentally select someone else's sheet or they type their password in wrong it will hide that sheet then move to the next one. That user won't know what the password is to the other sheet so they'll end up hiding every sheet in the workbook. I could hide every sheet and let the users unhide their sheet and type in the password but they would need to hide the sheet when they're done with it and I don't trust that they will do that. What I need is either a modified version of the code I'm currently using or a new version that would bring them back to sheet1 (the only unprotected sheet in the workbook) if the password is incorrect.

The code I'm currently using to password protect each sheet is:

Private Sub Worksheet_Activate()
Const Password As String = "Jan"
If InputBox("Please input the password to view this sheet.") <> Password Then
Me.Visible = xlSheetHidden
Else
Me.Visible = xlSheetVisible
End If
End Sub

And I'm also using this code to make sure the file always opens to sheet1:

Private Sub Workbook_Open()
Worksheets("Quick Look").Select
End Sub

Well hiding the pages if the password is entered incorrectly isn't ideal, that's why I posted here. But there is nothing stopping them from unhiding the sheet and trying again, there is also nothing stopping them from viewing the code and seeing all the passwords. Security isn't really the issue here, the passwords act more to prevent the users from accidentally changing someone else's data than anything else. It also stops them from viewing some confidential information but that isn't quite as important.

The sheet is visible when the password is prompted. I've made groups for each sheet so that, when collapsed, the only thing that's visible are employee names. So as long as the groups are collapsed seeing the sheets fine, though I haven't been able to get all of the groups to be collapsed when the file is opened but that's a problem for another post.

Each user does have a username and that would work well, but, I don't have access to that information as it is "above my pay grade." The file is in a secured drive though so only those users will be able to see the file anyway.

This file actually has a lot of problems because of the things my boss wants it to be able to do and if you're interested in seeing about it in more detail I made a post asking for help about it on Reddit (https://redd.it/59boyn) that has been unsuccessful so far. I didn't post all of those problems here though because people seem to get sidetracked by the minor problems and nothing useful ever gets solved for me.

Would it be possible to see a desensitized version of the file?

It would make it far easier to help you with the issues above, as well as the ones you highlight on Reddit.

BSB

Sure, give me a second and I'll have that done

Actually I don't know how to share the file with you...

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Remember to desensitize the data.

BSB

Here is the desensitized version. There are normally 19 sheets but I cut it down because it only takes a couple to get the general idea and I didn't want to go through that much information.

That would work very well, how could I do that?

In the attached I've applied sheet level protection with passwords using "Review / Protect Sheet". These passwords are currently set to the same as you had in the VBA in your example workbook.
Sheets can be unprotected via "Review / Unprotect Sheet" using the relevant password.

The code below is placed in the ThisWorkbook module (the same place you had your workbook open event code).

Please Login or Register  to view this content.

The code will fire each time the file is saved. The first part of the code will loop through all sheets in the workbook and close any expanded groups.
The second part will protect all sheets with specified passwords.

If you lock the VBA down via "Tools / VBAProject Properties / Protection" in the VB Editor then the passwords are safe from prying eyes (well as safe as Excel gets!).

Is that of any use to you?

BSB

That is perfect! Thank you so much for your help!

No problem at all. Happy I could help. Thanks for the rep point

Don't forget to mark the thread as SOLVED if you're happy you have a working solution. Select Thread Tools from the menu link above and choose SOLVED.

Also, a little tip for next time, don't forget to use code tags when you post your VBA or you'll face the wrath of The Moderators!

Have a good day.

BSB