HW#19 - Employee Database

The following spreadsheet is use to keep track of the employees working for a company:

When the user clicks on the Add Emp button, it is supposed to bring up a custom user form that looks like this:

If the user clicks OK on this form, the new employee data are copied to the appropriate columns of the next available row in the table. If the user clicks on the Delete Emp button, the following user form is displayed:

If the OK button is selected, the highlighted employee is deleted from the table.

Click here to download a copy of this spreadsheet.

Note that cell F4 (named numemp) contains a formula with the COUNT function. This formula returns the number of employees in the table. You will need to reference this in your code.

1. Create the frmAddEmp form using the layout shown above.

a. Write the VB code for the click event for the Cancel button inside the frmAddEmp form. This code should simply make the form go away.

b. Write the VB code for the click event for the OK button inside the frmAddEmp form.

This code should first check to see if the user has entered a value in the Last name textbox. If not (i.e., if the textbox is blank), you should display an error message using MsgBox and the user form should stay up. Don't worry about checking the other controls. Otherwise (the last name is not blank), you should make the form go away and then copy the data in the controls of the form into the next empty row in the table.

As part of determining the row number of the next empty row, you will need to reference the value in the numemp cell. Since your code is in a form, you will need to explicitly reference the sheet (Sheets("Sheet1").Range...). You should write either a Y or N for the Trained column based on the status of the Trained check box.

When you copy the value of the txtSalary box to the Salary column, you may want to pass the txtSalary value to the Val function (i.e., ... = Val(txtSalary)). Otherwise, it puts the salary in the cell as a string value and the formatting gets messed up

2. Write the VB code for the click event for the Add Emp button so that it brings up the frmAddEmp form.

3. Create the frmDelEmp form using the layout shown above.

a. Change the RowSource property for the list box control to reference the range A7:A50.

b. Write the VB code for the Cancel button for the frmDelEmp user form. This code should simply make the form go away.

c. Write VB code for the OK button for the frmDelEmp user form. This code should look at the ListIndex property of the list control to determine what employee was selected and then delete the entire row corresponding to that employee from the table. It should not just make that row go blank, it should delete the entire row. To determine how to delete a row, look up the Rows property in the Microsoft Visual Basic Help in the VB Editor.

4. Write the VB code for the click event for the Delete Emp button so that it brings up the frmDelEmp form.

Submittal Instructions:

1. Be sure to save the changes to your spreadsheet.

2. Upload your spreadsheet via Learning Suite.