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
sir
thank you for your very good video sir i need your help about vba i have xcell file contains( 6 sheets, sheet 1 main sheet, sheet 2 name client, sheet 3 client etc…) sheet 1 is main sheet this sheet (1) contain 2 column first column for data for example months from jan to december and second column for price i want to transfer or copy price column to another sheets According to name of client Knowing that he will be copied or deport prices many times a column to the columns in the customers sheets
Hi Can you please explain all the missing steps to your video from the beginning where you actual create the login box and link it to the correct tab.
without this information the rest of the video wont work