References:
A system admin collects and stores user account information in an Excel file. You can download the sample input file here.
Each user's information is stored in one row in the Excel file, starting from row 2. Row 1 is the title row. From column A to column F, the information includes username (aka login name), user's full name, password, the date the account was created, the date the account should expire, and the date when the password of this account was last changed.
Note that, typically passwords are encrypted before they are stored in a file. But for the purpose of this assignment, we store un-encrypted passwords in the Excel file.
Your Tasks:
You are asked to design and implement a VBA userform interface to help users change their passwords.
The userform should have 4 textboxes to collect the following information from a user who wants to change his/her password:
There should be one command button on the userform captioned as "Change Password".
Then, you need to develop a VBA subroutine to handle the command button click event. This subroutine should first validate the user's identity by checking whether the given old password matches with the current password of the account with the given username. If they match, then your program should check whether the given new password meets the following criteria:
If the new password fully meets the criteria, then your program should update the account's password to the new one, and update the last password change time to today. (Note that you can use the function Date() to read the current system date, and function Now() to read the current system time and date.)
It goes without saying that passwords must be case sensitive. To keep things simple, we'll follow the Linux convention and assume that usernames are also case sensitive.
Use a message box to notify the user whether the password change has been performed successfully.
Once the user successfully updated his/her password, then for security purposes, this userform should unload itself.
To process the new password validation, you may need to use the following string functions:
In your VBA program, you MUST develop and use at least the following procedures (note that you have to add the parameter passing type for each parameter to complete this assignment):
Function validPassword(str as String) as Boolean
'this function test whether the character string passed in through
'parameter str fully meets the criteria to be a valid password.
'function returns True if the string meets the criteria (except
'comparing with the re-type), and False otherwise.
End Function
Function isValidAccount(username as String, row as Integer) as Boolean
'this function returns true if the string value passed through
'parameter username exists in the data collection, and
'the row number where the account with the given username
'exists will be passed back through the parameter row;
'if the username does not exist in the data collection,
'then this function will return false and the parameter
'row would carry garbage data and shouldn't be used
End Function
Sub updatePassword(newPassword as String, destRow as Integer)
'this subroutine uses the new password passed into this subroutine
'through parameter newPassword to update the password of an account.
'the row number of the account is passed into this subroutine
'through the parameter destRow.
'this subroutine also needs to update the last update time of the account.
End Sub
The above 3 procedures can be implemented in the same userform code space where you implement your command button click subroutine.
Note that you'll get at most half of this assignment's marks if you don't follow the specification to implement and use the above 3 functions.
Before submitting your assignment, you need to check at least the following things:
Submitting:
To submit your assignment, name your solution file your Lastname_A10.xlsm, then login to your VIU Learn account, find the CSCI 112 course page, click on the "Assessment" drop-down menu, click on the "Assignments" item, then click on the folder named "A10". Then you can click on the "Add a File" button to browse and upload your solution file Lastname_A10.xlsm.
If you have difficulties to make the VIU Learn submission work, please email me to let me know and attach your Excel solution file with your email.