How do I create a macro to gather username & password, store on a sheet, and acess with a seperate macro for verification?

Dakcenturi said:
>>2) Macro stores the username and password on a hidden password protected sheet

You do realize that formulas can reference hidden worksheets (even "very hidden" worksheets), right?

All it will take for someone to defeat your security here is a simple formula.

I'm not worried about extreme security, just want to use this to hold usernames and passwords that can be accessed by another macro. I'm not trying to protect secure data or anything.

The basic premise is that I am creating a method to capture an electronic signature on sheets in a workbook, but I want the signature to put in the persons name based off the username and password that has been input in the sign off macro.

No suggestions?

If it helps at all, this is the current code I am using to create the signoff.

Sub Sign_Off_PDT()
Worksheets("PDT").Cells(5, 6) = Worksheets("Instruction Sheet").Cells(5, 2)
Worksheets("PDT").Cells(6, 6) = Now()
ActiveSheet.Protect Password:="password"
End Sub

Worksheets("Instruction Sheet") is the name of the person working on the sheet that is typed in.

Hi
Here is a simple solution.

Regards,
Stellan
UserNameAndPassword.xls

Great that looks like it will work perfectly!

One further question instead of the password screen disappearing when they input a wrong password I would want them to be able to try to re-enter the password for that username. I was thinking of just using a Do Loop While and an additional If statement.

Please let me know if this looks correct or if I am missing something.

Sub VerifyUserNameAndPassword()
    
    Dim sUserName As String
    Dim sPassword As String
    Dim wsTbl As Worksheet
    Dim rngUserNameCell As Range
    Dim sStoredPassword As String
    Dim pwattempt As Long

    pwattempt = 0
    
    
    'Get input from user
    sUserName = InputBox("Enter User Name")
    If sUserName = "" Then Exit Sub
    sPassword = InputBox("Enter Password")
    If sPassword = "" Then Exit Sub
    
    'Lookup the UserName
    Set wsTbl = Sheet2
    With wsTbl
        Set rngUserNameCell = .Columns(1).Find( _
                what:=sUserName, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
    End With
    
    If rngUserNameCell Is Nothing Then 'Did not find User Name
        MsgBox ("User Name Not Found!")
        Exit Sub
    Else
        sStoredPassword = rngUserNameCell.Offset(0, 1).Value
        'compare passwords
        Do
           If StrComp(sStoredPassword, sPassword, vbBinaryCompare) = 0 Then
              MsgBox ("User Name and Password Verified!")
              pwattempt = 3
           Else
	      MsgBox ("Wrong Password!")
              pwattempt = pwattempt + 1
              sPassword = InputBox("Enter Password")
              If sPassword = "" Then Exit Sub
           End If
        Loop While pwattempt < 3
        If pwattempt = 3 Then
           Exit Sub
    End If
        
        
End Sub

Select allOpen in new window

HI Dakcenturi,
It looks fine, except that you are missing one 'End If' statement as indicated in the code snippet.
Kind regards,
Stellan

Sub VerifyUserNameAndPassword2()
    
    Dim sUserName As String
    Dim sPassword As String
    Dim wsTbl As Worksheet
    Dim rngUserNameCell As Range
    Dim sStoredPassword As String
    Dim pwattempt As Long

    pwattempt = 0
    
    
    'Get input from user
    sUserName = InputBox("Enter User Name")
    If sUserName = "" Then Exit Sub
    sPassword = InputBox("Enter Password")
    If sPassword = "" Then Exit Sub
    
    'Lookup the UserName
    Set wsTbl = Sheet2
    With wsTbl
        Set rngUserNameCell = .Columns(1).Find( _
                what:=sUserName, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
    End With
    
    If rngUserNameCell Is Nothing Then 'Did not find User Name
        MsgBox ("User Name Not Found!")
        Exit Sub
    Else
        sStoredPassword = rngUserNameCell.Offset(0, 1).Value
        'compare passwords
        Do
           If StrComp(sStoredPassword, sPassword, vbBinaryCompare) = 0 Then
              MsgBox ("User Name and Password Verified!")
              pwattempt = 3
           Else
          MsgBox ("Wrong Password!")
              pwattempt = pwattempt + 1
              sPassword = InputBox("Enter Password")
              If sPassword = "" Then Exit Sub
           End If
        Loop While pwattempt < 3
        If pwattempt = 3 Then
           Exit Sub
        End If 'WAS MISSING
    End If
        
        
End Sub

Select allOpen in new window