Create a program based on the passwords.xlsx file that has each feature specifie

Question

Create a program based on the passwords.xlsx file that has each feature specified below. Be sure to submit the workbook with the code that you have created (passwords.xlsm).  Your program must:

Determine if each password in column A of the Password list worksheet is valid or not. If a password is valid, put the string “Yes” in column B of the corresponding row and if it is not valid, put the string “No” bolded and in red color font in column B of the corresponding row. In order to be valid, the password needs to be at least 8 characters long and consist of at least one character that is not a letter, but no more than 2 numbers. In addition, the password must contain at least one lower case letter.

Have code that will work even if the number of passwords changes (either fewer or more passwords in the list). Have a message box that informs the user how many passwords there are in the list and how many are not valid (If no passwords in the list, informs user there are no passwords).

Place a button in the worksheet that will execute the code when clicked on by the user.

Have comments that explain what is happening at each step as well as one in the beginning of your code that has your name and the date the code was created.

Passwords Valid Chicago15 loy2015Chi chicago# LoyolaChicago HELLO123 HELLO123a Chic15

Explanation / Answer

Sub CheckPasswords()

Dim num_Rows As Integer

num_Rows = Worksheets("Sheet1").Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Rows.Count

'informs if no passwords in sheet
If num_Rows = 0 Then
    MsgBox "No passwords in the list."
End If

Dim counter As Integer

Dim invalid As Integer

invalid =0

'loop to check validity of passwords
For counter = 2 To num_Rows

    'Worksheets("Sheet1").Range("B" & counter).Formula = Worksheets("Sheet1").Range("A" & counter).Formula
    Dim passwd As String
    passwd = Worksheets("Sheet1").Range("A" & counter).Formula
    Dim regEx As New VBScript_RegExp_55.RegExp
    Dim matches, s
    regEx.Pattern = "^(?=.*[a-z])(?=.*d{1,2})[a-zA-Zd]{8,}$"
    'checks regular expression
    If regEx.Test(passwd) Then 'if password is valid
        Worksheets("Sheet1").Range("B" & counter).Formula = "Valid"
    Else 'if password is not valid
        Worksheets("Sheet1").Range("B" & counter).Formula = "No"
        Worksheets("Sheet1").Range("B" & counter).Font.Bold = True
        Worksheets("Sheet1").Range("B" & counter).Font.Color = vbRed

        invalid=invalid+1
    End If
Next

MsgBox "Total passwords in the list:" & (num_Rows - 1) & ", Total invalid passwords:" & invalid
End Sub

--------------------------

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.