change in the VBA code for login in excel

[abdulncr]

HI Friends,


I have below code to login with user id and pw in excel.


this work with only one user id, i have 5 user id and password which should take the user to different sheet with successfull login. eg: when login with user1, will take to sheet3 let the user do the update, when login with user2, should take sheet4 and so on

and also if the sheet is hidden, it is not going to concerned sheet.


it would be great any one can give a change in the below code.


Private Sub CommandButton1_Click()

Dim username, password As String


username = TextBox1.Text

password = TextBox2.Text


If username = "user1" And password = "11111" Then

MsgBox "logined successfully", vbInformation

Unload Me

Sheets("Sheet3").Select


Else

MsgBox "incorrect id and pw", vbCritical


ActiveWorkbook.Close


End If

End Sub


Thanks


Abdul Jaleel

 

[Luke M]

Here's an example of a way you could do it.

[pre]

Private Sub CommandButton1_Click()
Dim username, password As String
Dim mySheet As String

username = TextBox1.Text
password = TextBox2.Text

Select Case username

Case "user1"
If password = "1111" Then
mySheet = "Sheet1"
Else
GoTo Invalid
End If

Case "user2"
If password = "2222" Then
mySheet = "Sheet2"
Else
GoTo Invalid
End If
Case "user3"
If password = "3333" Then
mySheet = "Sheet3"
Else
GoTo Invalid
End If
Case "user4"
If password = "4444" Then
mySheet = "Sheet4"
Else
GoTo Invalid
End If
Case "user5"
If password = "55555" Then
mySheet = "Sheet5"
Else
GoTo Invalid
End If
Case Else
GoTo Invalid
End Select
MsgBox "logined successfully", vbInformation
Unload Me
Sheets(mySheet).Select

Exit Sub
Invalid:
MsgBox "incorrect id and pw", vbCritical
ActiveWorkbook.Close
End Sub

[/pre]

 

[Colin Legg]

As an alternative you could consider using Windows authentication: just get the user name using the GetUserName API and then select the appropriate sheet. The advantages of this are that it is more convenient for your users (they don't have to type in a username and password) and it simplifies your code (you can remove the userform entirely).

 

[abdulncr]

Dear Luke,


It is great help, code is working fine.really obliged to you.


Regards


Abdul Jaleel