Veryhidden Sheets based on Usernamei and Password

[GLower0617]

I found this code searching forums and I have a specific use for it and I am trying to get it to function. I have only made minor changes to it so that it recognized the user row to determine which sheets to unlock. The idea is to prompt for username and password and then unhide the sheets that that particular user has access to. It matches the username in the user name column and then unhides the sheets that are listed in the same row as the username. I have it fully functioning now except that it will not unhide the last sheet in the loop. If there is only 1 sheet that the user is allowed to have access to then it will not unhide it as it sees it as the last sheet in the row. Not sure why. Any help is appreciated. Below is the sheet and the code.

Gary

User Name	Password	Sheet	Sheet	Sheet	Sheet	Sheet	Sheet	Sheet
PlantMgr	0000	Roster	Leadership	A-Crew	B-Crew	C-Crew	D-Crew	UserList
ProdSup	1111	Roster	Leadership	A-Crew	B-Crew	C-Crew	D-Crew	UserList
QualSup	2222	Roster	Quality
TeamLead1	3333	Roster	A-Crew
TeamLead2	4444	Roster	B-Crew
TeamLead3	5555	Roster	C-Crew
TeamLead4	6666	Roster	D-Crew

<colgroup><col><col><col><col><col span="5"></colgroup><tbody>
</tbody>

Private Sub Workbook_Open()
Dim Sh As Worksheet
Dim UserName As String
Dim Password As String
Dim ThisCell As Range
Dim UserRow As Long
Dim C As Long

For Each Sh In ThisWorkbook.Worksheets
    If Sh.Name <> "Roster" Then
        Sh.Visible = xlSheetVeryHidden
    End If
Next Sh

UserName = InputBox("Please enter Username")
Password = InputBox("Please enter password")

For Each ThisCell In Sheets("UserList").Range("A2:A" & Sheets("UserList").Range("A50000").End(xlUp).Row)
    If UCase(ThisCell.Value) = UCase(UserName) And UCase(ThisCell.Offset(, 1).Value) = UCase(Password) Then
    MsgBox "Access Granted"
        For C = 3 To 11
            UserRow = ThisCell.Row
            If ThisCell.Offset(, C).Value <> "" Then
            Sheets(Sheets("UserList").Cells([UserRow], C).Value).Visible = xlSheetVisible
            End If
        Next C
        Exit Sub
    End If
Next ThisCell

MsgBox "Access Denied"
ThisWorkbook.Close

End Sub

 

[Gingertrees]

I assume the usernames and passwords are not case sensitive?

I'm not sure why the last sheet in the range isn't showing - I suggest streamlining the code, and removing ambiguities. These include adding a Lastrow definition, and clarifying any offsets:
**Lastrow**
Add in LastRow, use that in your code for improved performance:

Dim Lastrow as Integer
Lastrow = Sheets("UserList").Range("A50000").End(xlUp).Row
Dim Range1 as range
Set Range1 = Sheets("UserList").Range("A2:A" & Lastrow)

For each ThisCell in Range1 

***Offsets***
I know offset(, 3) technically works, but any ambiguities sometimes cause ghosts in the machine. Change any Offset(, 3) to Offset(0, 3). (Same for other numbers where you just have comma.)

See if that helps?

 

[GLower0617]

Thanks for the suggestion. I did make the changes you suggested to help clean up the code. I was able to make a workaround for the original problem. I simply added a blank worksheet and named it IntentionallyBlank. By adding it to the sheets that should be unhidden then it simply unhides everything except the last sheet which is IntentionallyBlank. Simple workaround. I would still like to understand why it does not unhide the last sheet.