VBA code not matching user logins and passwords

[greenhillchris]

Hi, I have a workbook with different sheets and only want certain users to see ceratin sheets. I have usernames and passwords on a login sheet within this book. I have got the following code entered in the form however evertime I run it and enter the usernames and passwords they are not recgoinsed and cant login. Not sure where I am going wrong! Any help on why the code is working would be a great help.

[
Option Explicit
Private Trial As Long
Private Sub cmdCheck_Click()

Dim AddData As Range, Current As Range
Dim user As Variant, Code As Variant
Dim PName As Variant, AName As Variant
Dim ws As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim result As Integer
Dim TitleStr As String
Dim msg As VbMsgBoxResult

user = Me.txtUser.Value
Code = Me.txtPass.Value
TitleStr = "Password check"
result = 0

If user <> "" And Not IsNumeric(user) And Code <> "" And IsNumeric(Code) Then
For Each AName In Sheet9.Range("J2:J10")
If AName = Code Then
Sheet9.Visible = True
Sheet9.Select
Unload Me
Exit Sub
End If
Next AName
End If

If user <> "" And Not IsNumeric(user) And Code <> "" And IsNumeric(Code) Then
For Each PName In Sheet9.Range("b2:b108")
If PName = Code Then

If PName.Offset(0, 1) <> "" Then
Set ws = Worksheets(PName.Offset(0, 1).Value)
ws.Visible = True
End If

If PName.Offset(0, 2) <> "" Then
Set ws2 = Worksheets(PName.Offset(0, 2).Value)
ws2.Visible = True
End If

If PName.Offset(0, 3) <> "" Then
Set ws3 = Worksheets(PName.Offset(0, 3).Value)
ws3.Visible = True
End If

ActiveWindow.DisplayWorkbookTabs = True
result = 1
Unload Me
End


Exit Sub
End If
Next PName
End If

If result = 0 Then

Trial = Trial + 1

If Trial < 3 Then msg = MsgBox("Wrong password, please try again", vbExclamation + vbOKOnly, TitleStr)
Me.txtUser.SetFocus

If Trial = 3 Then
msg = MsgBox("Wrong password, the form will close…", vbCritical + vbOKOnly, TitleStr)
ActiveWorkbook.Close False
End If
End If
End Sub



]

 

[6StringJazzer]

Your code has no comments to explain how you want it to work, and you have not explained it in your post either, and you have not described your data layout.

I do not see how this code is checking for a valid combination of user and password. You will have to explain how it is doing that.

First it looks at the password the user provided (Code) and looks for it in Sheet9.Range("J2:J10"). If it finds it, it makes Sheet9 visible then exits.

Then it looks at the password the user provided (Code) and looks for it in Sheet9.Range("b2:b10"). If it finds it, it makes up to three worksheets visible, assuming that columns C, D, E, contain worksheet names.

It never checks the value of the userid (user), except to see that is not blank and not numeric.

None of that ever checks for a valid user/password combination.

What data is in Sheet9.Range("J2:J10")?
What data is in Sheet9.Range("b2:b108")
What data is in Sheet9.Range("C2:C108")?
What data is in Sheet9.Range("D2:D108")?
What data is in Sheet9.Range("E2:E108")?

 

[greenhillchris]

Thanks for the reply

Sorry let me explain in more detail. It’s one workbook and each user I add should have their own 3 worksheets, when they login in they should only see their own sheets and not other sheets in the workbook. There should be a admin login and they should see the whole workbook.

How you have explained the codes has helped me, sheet 9 is the sheet with the user and password details.

The data in Sheet9.Range("J2:J10") is the admin passwords and column (I) before it is the username, I think I need to add an offset to pick up the username? I also need the admin log in to see all of the workbook, so need to change that to Workbook rather than just sheet 9.

The data in Sheet9.Range("b2:b108") is the user passwords and column (A) before it is the usersname and they should only see their 3 sheets. Again I think I need to add an offset, I won’t need 107 users, just put a random number to help for future users added.

In Sheet9.Range("C2:C108"), ("D2:D108") & ("E2:E108") they are the sheet names that the user has access too.

Thanks

 

[6StringJazzer]

I cannot test this code without your form or your file but I would try this. Also, I highly recommend code tags to improve the readability of your code.

Option Explicit
Private Trial As Long
Private Sub cmdCheck_Click()

   Dim AddData As Range, Current As Range
   Dim User As Variant, Code As Variant
   Dim FoundAdmin As Range, FoundUser As Range
   Dim ws As Worksheet
   Dim Success As Boolean
   Dim TitleStr As String
   Dim msg As VbMsgBoxResult
   
   User = Me.txtUser.Value
   Code = Me.txtPass.Value
   TitleStr = "Password check"
   
   If User <> "" And Not IsNumeric(User) And Code <> "" And Not IsNumeric(Code) Then
   
      Set FoundAdmin = sheet9.Range("I2:I10").Find(what:=User, Lookat:=xlWhole)
      
      If Not FoundAdmin Is Nothing Then
      
         ' Found admin user name, check password
         If sheet9.Cells(FoundAdmin.Row, "J") = Code Then
         
            ' user name and password match
            ' Make all sheets visible
            For Each ws In Worksheets
               ws.Visible = True
            Next ws
            
            Success = True
         Else
            ' wrong password for this user
         End If
      
      Else
      
         Set FoundUser = sheet9.Range("A2:A108").Find(what:=User, Lookat:=xlWhole)
         
         If Not FoundUser Is Nothing Then
         
            ' Found user name, check password
            If sheet9.Cells(FoundUser.Row, "B2:B108") = Code Then
            
               ' user name as password match, make selected sheets visible
               
               If FoundUser.Offset(0, 2) <> "" Then
                  Worksheets(PName.Offset(0, 2).Value).Visible = True
               End If
               
               If FoundUser.Offset(0, 3) <> "" Then
                  Worksheets(PName.Offset(0, 3).Value).Visible = True
               End If
               
               If FoundUser.Offset(0, 4) <> "" Then
                  Worksheets(PName.Offset(0, 4).Value).Visible = True
               End If
               
               ActiveWindow.DisplayWorkbookTabs = True
                  result = 1
               End
               
               Success = True
            
            Else
               ' wrong password for this user
            End If
         Else
            ' user name not found
         End If
         
      End If
   
   End If
   
   If Success Then
   
      ' All done, don't need this form anymore
      Unload Me
   
   Else
   
      Trial = Trial + 1
      
      If Trial < 3 Then msg = MsgBox("Wrong user/password combination, please try again", vbExclamation + vbOKOnly, TitleStr)
      Me.txtUser.SetFocus
      
      If Trial >= 3 Then
         msg = MsgBox("Wrong user/password combination, the form will close…", vbCritical + vbOKOnly, TitleStr)
         ActiveWorkbook.Close False
      End If
   End If

End Sub

 

[greenhillchris]

Legend! Thanks so much

Had to do a minor tweak to the user code, works a perfectly

 Set FoundUser = Sheet9.Range("A2:A108").Find(what:=User, Lookat:=xlWhole)
         
         If Not FoundUser Is Nothing Then
         
            ' Found user name, check password
            If Sheet9.Cells(FoundUser.Row, "B") = Code Then
            
               ' user name as password match, make selected sheets visible
               
               If FoundUser.Offset(0, 2) <> "" Then
                  Worksheets(FoundUser.Offset(0, 2).Value).Visible = True
               End If
               
               If FoundUser.Offset(0, 3) <> "" Then
                  Worksheets(FoundUser.Offset(0, 3).Value).Visible = True
               End If
               
               If FoundUser.Offset(0, 4) <> "" Then
                  Worksheets(FoundUser.Offset(0, 4).Value).Visible = True
               End If
               
                     ActiveWindow.DisplayWorkbookTabs = True
                 
               
               Success = True

 

[6StringJazzer]

I'm lucky that a minor tweak was all that was needed! I just made sure it compiled and didn't test.