VBA Code to Run a Pin Number Checker

[MPORTER444]

I am attempting to find a code to require Pin Number for User access to run the starting macros for the program.

On Sheets("Passwords") Column B contains list of the usernames (B2:B200). Column C contains the Pin Numbers (C2:C200).

On Sheets("Log On") User enters Username and Pin Number. Cells("C5") = UserName. Cells("C62") = Pin Number.

If the username entered equals the Pin Number on the same row, it will run the desired macro - StartProgram

I hope this make sense. Thank you for any help.

 

[saboh12617]

Hello,

You can proceed as follow:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Range("C5,C62")) Is Nothing Then Exit Sub
  
  If CheckID(Range("C5").Value2, Range("C62").Value2) Then StartProgram
End Sub

Function CheckID(userName As String, pin As String) As Boolean
  Dim i As Long
  With ThisWorkbook.Worksheets("Passwords").Range("B2:C200").Rows
    For i = 1 To 199
      CheckID = (.Cells(i, 1) = userName And .Cells(i, 2) = pin)
      If CheckID Then Exit Function
    Next i
  End With
End Function

However, a very important remark:
Assigning credentials in a workbook is everything but safe and secure. I hope it is more a "local" program than anything else, because everyone with a bit of knowledge can access the hidden data of a workbook. If you aim to design something robust you should not use XL/VBA as an authenticator.

 

[MPORTER444]

I thank you for that advice. It is a local program just looking to add a layer of accountability.

Is it possible to have code in the form of a macro that will fire after user clicks a button? User enters Payroll Number in C5, then Passcode in C62. Then clicks a button to fire macro.

I thank you very much for you time and advice.

 

[saboh12617]

Hello,
Yes, put the code below in a Module (not the sheet code), and link your button to the Sub "CheckAuth" below :

Public Sub CheckAuth()
  With Worksheets("Log On")
    If CheckID(.Range("C5").Value2, .Range("C62").Value2) Then StartProgram
  End With
End Sub


Private Function CheckID(userName As String, pin As String) As Boolean
  Dim i As Long
  With ThisWorkbook.Worksheets("Passwords").Range("B2:C200").Rows
    For i = 1 To 199
      CheckID = (.Cells(i, 1) = userName And .Cells(i, 2) = pin)
      If CheckID Then Exit Function
    Next i
  End With
End Function

 

[MPORTER444]

Hello,
Yes, put the code below in a Module (not the sheet code), and link your button to the Sub "CheckAuth" below :

Public Sub CheckAuth()
  With Worksheets("Log On")
    If CheckID(.Range("C5").Value2, .Range("C62").Value2) Then StartProgram
  End With
End Sub


Private Function CheckID(userName As String, pin As String) As Boolean
  Dim i As Long
  With ThisWorkbook.Worksheets("Passwords").Range("B2:C200").Rows
    For i = 1 To 199
      CheckID = (.Cells(i, 1) = userName And .Cells(i, 2) = pin)
      If CheckID Then Exit Function
    Next i
  End With
End Function

You are amazing. Thank you.

 

[saboh12617]

Glad to help, have a good day.