How to check login, password and open specific worksheet with VBA. A user wants to be able to login with his user name and password. Another worksheet contains a list of user names and passwords. As soon as he inputs his user name and password, these credentials are checked against the already existing data. If the user name (login) and password match then he can view a worksheet with his user name. Watch the training video.

Watch this video on YouTube.

Here’s the complete VBA code:

Private Sub Workbook_Open()
Dim i, LastRow
LastRow = Sheets(“UserData”).Range(“A” & Rows.Count).End(xlUp).Row
Sheets(“Login”).Visible = xlSheetVisible
For Each ws In Sheets
If UCase(ws.Name) <> “LOGIN” Then
ws.Visible = xlSheetVeryHidden
End If
Next
retry:
ulogin = UCase(InputBox(“Please enter your username.”, “Username”))
If ulogin = “” Then
MsgBox “No username entered. Closing application.”, vbCritical, “Close”
Application.DisplayAlerts = False
Application.Quit
Exit Sub
End If
uPasswd = InputBox(“Please enter your case sensitive password.”, “Password”)
If uPasswd = “” Then
MsgBox “No password entered. Closing application.”, vbCritical, “Close”
Application.DisplayAlerts = False
Application.Quit
Exit Sub
End If
For i = 2 To LastRow
chkCred = UCase(Sheets(“UserData”).Cells(i, “A”).Value) & _
Sheets(“UserData”).Cells(i, “B”).Value
If chkCred = ulogin & uPasswd Then
For Each ws In Sheets
‘ws.Visible = xlSheetVisible
If UCase(ws.Name) = ulogin Then
ws.Visible = xlSheetVisible
End If
Next ws
Sheets(“Login”).Visible = xlSheetVeryHidden
Sheets(“UserData”).Visible = xlSheetVeryHidden
recFound = “Yes”
Else
End If
Next i
If recFound <> “Yes” Then
tryAgain = MsgBox(“Username and Password not found. Try again?”, _
vbCritical + vbYesNo, “No Record”)
Select Case tryAgain
Case Is = vbYes
GoTo retry
Case Is = vbNo
Application.DisplayAlerts = False
Application.Quit
Exit Sub
End Select
End If
End Sub

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If Sheets(“UserData”).Visible = xlSheetVisible Then
Sheets(“UserData”).Visible = xlSheetVeryHidden
Target.Offset(1).Select
Exit Sub
End If
Passwd = InputBox(“Enter the access password”, “Password”)
If Passwd = “OpenMeUp” Then
Sheets(“UserData”).Visible = xlSheetVisible
Else
MsgBox “Invalid entry!”, vbCritical, “Close”
End If
Target.Offset(1).Select
End Sub

 

Reference: Excel VBA – Username and Password from Lookup?