PDA

View Full Version : Solved: HELP - Multiple User Worksheet - Code Amend



SDave
12-16-2009, 04:12 AM
Morning all,

I have adapted a code created by DRJ but for the life of me I can't get it to function correctly.

I have attached a copy of my workbook FYI.

The code is meant to release a specific worksheet dependant on user details.

Code:


Option Explicit
Private Sub Cancel_Click()
Unload Me
End Sub
Private Sub OK_Click()
Dim c As Object
Dim UserName As String
Dim Password As String
Dim MyError As Integer

'Assign UserName to a variable
UserName = UserVerification.UserName.Text

'Assign Password to a variable
Password = UserVerification.Password.Text

'Specify the sheet and range to look up the UserName
With Sheets("UserNames").Range("A:A")
Set c = .Find(What:=UserName, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
End With

'If UserName cannot be found
If c Is Nothing Then
MsgBox "The username you have inputted is invalid, please try again.", vbCritical, "Incorrect UserName"
Exit Sub
End If

'If Password is correct
If Password = Sheets("UserNames").Range(c.Address).Offset(0, 1).Text Then
ThisWorkbook.Unprotect Password:="Password"

'If the sheet to make visible is not in the workbook an error will occur
'If there is no error then make that sheet visible
On Error Resume Next
Sheets(Range(c.Address).Offset(0, 2).Text).Visible = True
MyError = Err
On Error GoTo 0
ThisWorkbook.Protect Password:="Password", Structure:=True

'If there was an error let the user know to try again.
If MyError <> 0 Then
MsgBox "The sheet specified could not be shown, please contact your system administrator.", vbCritical, "Sheet Specification Error"
Exit Sub
End If
Else

'If the password is incorrect let the user know to try again
MsgBox "The password you have specified is incorrect, please try again.", vbCritical, "Incorrect Password"
Exit Sub
End If

'Dismiss the userform
Unload Me
End Sub



The user details are as follows:

UserNamePasswordSheetNameUser1sWayA8RaTest1User2yU5acr5FTest2User33Acu3wewT est3User4xeHe7uchTest4User5ZUd8saPrTest5User6nu4H3qUwSummaryOverviewAdmin1A rkhamControlAdmin2GothamSummaryAdmin3BatmanUserNames

Instead of releasing the appropriate worksheet I seem to be receiving the following errors:

"The sheet specified cannot be shown, please contact your system administrator" or

"The username you have inputted is invalid, please try again"

I'm not to sure where I have gone awry?!

Any help would be much appreciated.

Thanks in advance.

Aussiebear
12-16-2009, 05:01 AM
What is this meant to be?
UserNamePasswordSheetNameUser1sWayA8RaTest1User2yU5acr5FTest2User33Acu3wewT est3User4xeHe7uchTest4User5ZUd8saPrTest5User6nu4H3qUwSummaryOverviewAdmin1A rkhamControlAdmin2GothamSummaryAdmin3BatmanUserNames

SDave
12-16-2009, 05:10 AM
Hi Aussiebear,

Apologies, when I first pasted that section in it was in the form of a table?!

I've managed to overcome the problem I was originally faced with, it would appear that all username and password information needs to sit within the same worksheet as the macro trigger?!