Hello!
I've been trying to get my login userform to login when clicked based on data in a table in the workbook, but I just can't seem to get the code right.
Details are:
Userform username textbox = UsernameTextbox
Userform password textbox = PasswordTextbox
Userform submit button = LoginButton
My workbook has a number of sheets, one of which is "Users". In that sheet, there is a table called "Users_Table". That table has 3 columns: Username [Column A], Password [Column B], Admin (answer is "True" or "False" depending on if they have admin rights) [Column C].
I'm trying to do this:
If the username and password is correct for a user AND if the admin column entry is False, then I want to show sheets "Quick Add" and "Overview", I want to make the sheet "Admin" hidden (not VeryHidden since I need to use data on this sheet for other macros), and make "User" sheets VeryHidden so those logged in can't see other users' details. But for users who correctly enter their username and password AND for whom the admin column entry is True, I want to show all sheets. If the username/password combo is incorrect, I want all sheets to be hidden.
Based on what I've found here, I've been playing with some code and I'm nearly there ( I think!). This is what I'm working with:
Private Sub LoginButton_Click()
Dim Username As String
Username = UsernameTextbox.Text
Dim password As String
Password = PasswordTextbox.Text
If IsNull(Me.UsernameTextbox) Or Me.UsernameTextbox = "" Then
MsgBox "You must enter your Username", vbOKOnly, "Incomplete Entry"
Me.UsernameTextbox.SetFocus
Exit Sub
End If
If IsNull(Me.PasswordTextbox) Or Me.PasswordTextbox = "" Then
MsgBox "You must enter your Password (case sensitive)", vbOKOnly, "Incomplete Entry"
Me.PasswordTextbox.SetFocus
Exit Sub
End If
Dim temp As String
On Error Resume Next
temp = WorksheetFunction.VLookup(Me.UsernameTextbox.Value, Worksheets("Users").Range("Users_Table"), 1, 0)
If Username = temp Then
Err.Clear
temp = ""
temp = WorksheetFunction.VLookup(Me.UsernameTextbox.Value, Worksheets("Users").Range("Users_Table"), 2, 0)
On Error GoTo 0
If Password = temp Then
Sheets("Quick Add").Visible = xlSheetVisible
Sheets("Overview").Visible = xlSheetVisible
Sheets("Admin").Visible = xlSheetHidden 'This is now just Hidden and not VeryHidden since other macros need to use data on this sheet
Sheets("Users").Visible = xlVeryHidden
MsgBox "Login Details Validated. You are now Logged In."
Unload Me
Sheets("Quick Add").Select
Range("A1").Select
Else
Sheets("Quick Add").Visible = xlVeryHidden
Sheets("Overview").Visible = xlVeryHidden
Sheets("Admin").Visible = xlVeryHidden
Sheets("Users").Visible = xlVeryHidden
MsgBox "Invalid Login Details"
End If
Else
Sheets("Quick Add").Visible = xlVeryHidden
Sheets("Overview").Visible = xlVeryHidden
Sheets("Admin").Visible = xlVeryHidden
Sheets("Users").Visible = xlVeryHidden
MsgBox "Invalid Username"
End If
End SubI'm just not sure how I'd go about adding in the Admin requirement mentioned above. I need Admins ("True" in "Admin" column, i.e. Column D, in the "Users_Table") to be able to see all sheets; the code above is just for Users and shows "Quick Add" and "Overview" but hides "Admin" and "Users".
I also think for the Admins I could Call this macro instead of specifying xlSheetVisible for all sheets, but I haven't got that far yet:
Sub Unhide_All_Sheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End SubI'm fairly new to VBA and I'm trying to teach myself, but I think I'm at an impasse.
Trying to do this is driving me mad. Any help at all would be much appreciated!
Thanks for looking and for your time.