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

Dakcenturi
Dakcenturi used Ask the Experts™
on
How would I make two macro's that do the following:

Macro 1:
1) Macro prompts for a username and password
2) Macro stores the username and password on a hidden password protected sheet

Macro 2:
1)Macro prompts for a username and password
2)Macro verifies the username and password match on the hidden password protected sheet where they are stored

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
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.

Author

Commented:
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.

Author

Commented:
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.
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Hi
Here is a simple solution.

Regards,
Stellan
UserNameAndPassword.xls
Hi again
Explanation:
The table with usernames and passwords is in a sheet which is xlVeryHIdden. The CodeName of this worksheet is Sheet2
If you need password protection of the hidden sheet you have to put Unprotect statement before writing new username and password and then Protect afterwards.
Here is the code.
/Stellan
Option Explicit

Sub StoreUserNameAndPassword()

    Dim sUserName As String
    Dim sPassword As String
    Dim wsTbl As Worksheet
    Dim rngNextCell As Range
    
    
    'Get input from user
    sUserName = InputBox("Enter User Name")
    If sUserName = "" Then Exit Sub
    sPassword = InputBox("Enter Password")
    If sPassword = "" Then Exit Sub
    
    'Store in hidden worksheet, UserName in col 1 and Password in col 2
    Set wsTbl = Sheet2
    With wsTbl
        Set rngNextCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
    End With
    rngNextCell.Value = sUserName
    rngNextCell.Offset(0, 1).Value = sPassword
        
    
End Sub


Sub VerifyUserNameAndPassword()
    
    Dim sUserName As String
    Dim sPassword As String
    Dim wsTbl As Worksheet
    Dim rngUserNameCell As Range
    Dim sStoredPassword As String
    
    
    '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
        If StrComp(sStoredPassword, sPassword, vbBinaryCompare) = 0 Then
            MsgBox ("User Name and Password Verified!")
        Else
            MsgBox ("Wrong Password!")
            Exit Sub
        End If
    End If
        
        
End Sub

Open in new window

Author

Commented:
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

Open 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

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial