Password Protecting each sheet for different users

[mcdbennett]

Scenario:
I have 1 workbook with 8 sheets. I only want certain people to be able to access each sheet to modify data. Here is where I am, but I cannot go further. It keeps giving this error Error.jpg marked in red below. Any suggestions?

Private Sub Workbook_aftersave(ByVal Success As Boolean)
    UnprotectSheet
    ThisWorkbook.Saved = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ProtectSheets
End Sub
Private Sub Workbook_Open()
    USERID = Environ("username")
    If USERID <> "Admin" Then ProtectSheets
    UnprotectSheet
End Sub
Private Sub ProtectSheets()
    Sheet1.Protect "password"
    Sheet2.Protect "cadpassword"
    Sheet3.Protect "manpassword"
    Sheet4.Protect "gaypassword"
    Sheet5.Protect "tvcpassword"
    Sheet6.Protect "cdhpassword"
    Sheet7.Protect "grhpassword"
    Sheet8.Protect "tchpassword"
End Sub
Private Sub UnprotectSheet()
    If (USERID = "kalton") Or (USERID = "sheater") Or (USERID = "aavery3") Or (USERID = "thughes") Or (USERID = "mbennett6") Then Sheet1.Unprotect "password"
    If (USERID = "aaben2") Or (USERID = "cjurick") Or (USERID = "tkline") Or (USERID = "mbennett6") Then Sheet2.Unprotect "cadpassword"
    If (USERID = "aaben2") Or (USERID = "cjurick") Or (USERID = "tkline") Or (USERID = "mbennett6") Then Sheet3.Unprotect "manpassword"
    If (USERID = "aaben2") Or (USERID = "jshaw6") Or (USERID = "mbennett6") Then Sheet4.Unprotect "gaypassword"
    If (USERID = "aaben2") Or (USERID = "jshaw6") Or (USERID = "mbennett6") Then Sheet5.Unprotect "tvcpassword"
    If (USERID = "aaben2") Or (USERID = "skillingbeck") Then Sheet6.Unprotect "cdhpassword"
    If (USERID = "aaben2") Or (USERID = "skillingbeck") Then Sheet7.Unprotect "grhpassword"
    If (USERID = "aaben2") Or (USERID = "skillingbeck") Then Sheet8.Unprotect "tchpassword"
    
End Sub

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

[6StringJazzer]

Have you declared USERID at the top as a global variable? This code will not work at all if you have not done at least that. Once I added that, the code worked for me with no errors. I was able to save, close, and reopen the file with no errors, with protection as expected. I changed one of the user names to Jeff so I could test it.

Aside from that, the variable USERID should be declared and assigned in each Sub. (You could declare it globally and assign it on workbook open but I do not recommend that, because a disruption to the VBA runtime will clear it.)

Also your logic on protection management is a bit backwards. You should save the workbook with all sheets protected, then unprotect only the appropriate sheets upon open. If someone tries to open this file after aaben2 closes it, and disables macros, they will have access to almost every sheet.

[mcdbennett]

Disclaimer: I am VERY new to VBA coding with no training. This is only the second time I have tried to do a spreadsheet with it

I tried googling what you suggested with thte global variable and I cannot figure it out. Would you be able to explain that? from what I could find it seems like I should have the first one as

Dim USERID as String

I am not sure how to change what you mentioned about protecting the sheets. What you are describing is my goal and I thought that is how I had it programmed

[6StringJazzer]

I tried googling what you suggested with thte global variable and I cannot figure it out. Would you be able to explain that? from what I could find it seems like I should have the first one as
Dim USERID as String

That is the line of code that I added in my test. If you do not have that, then add it at the top, before the first Sub.

In Workbook_Open you are assigning a value to USERID. In UnprotectSheet you are checking the value of that variable. But based on the code you showed here, those are two different variables that are each local to the subs where they are used, and your code will not work at all.

I strongly recommend to everyone that they use Option Explicit and declare variables. Doing so prevents a lot of bugs and runtime errors.

I am not sure how to change what you mentioned about protecting the sheets. What you are describing is my goal and I thought that is how I had it programmed

I'm very sorry to have caused this confusion, you are absolutely 100% right. I got tangled up in the before vs. after.