Creating the User Interface

If you skipped the previous chapter, you may need to turn back. Implementing your custom CEmployee object requires that you design a custom form.

1. Highlight the current VBA project in the Project Explorer window and choose Insert | UserForm.

2. Prepare the form shown in Figure 11-1:

Figure 11-1: This form demonstrates the use of the CEmployee custom object. 3. Set the following properties for the form and its controls:

Object

Property

Setting

UserForm1

Name

Salaries

Caption

Employees and Salaries

label1

Caption

Last Name

text box below the Last Name label

Name

txtLastName

label2

Caption

First Name

text box below the First Name label

Name

txtFirstName

label3

Caption

Salary

text box below the Salary label

Name

Property

Setting framel text box in the frame titled "Salary Modification"

option button 1 option button 2 frame2

option button 3 option button 4 list box command button 1 command button 2 command button 3 command button 4 command button 5

Caption Name

Name Caption

Name Caption

Caption

Name Caption

Name Caption

Name Height Width

Name Caption

Name Caption

Name Caption

Name Caption

Name Caption

Salary Modification txtRaise optPercent Percent (%)

optAmount Amount ($)

Change the Salary for optHighlighted Highlighted Employee optAll

All Employees lboxPeople

91.45

180.75

cmdSave Save cmdClose Close cmdUpdate Update Salary cmdDelete Delete Employee cmdEmployeeList Update List_

4. Prepare a data entry worksheet, as shown in Figure 11-2:

H chû|)11 .His

A

B

e-

1 D

1

Employee Salaries

2

Id

Lost N<ime

First Name

Salary

3

4

5

6

7

.Si

i Î t

y M \ Sheet! § 5heet2 / 5heet3 / 5heet4 \Sai | ^ |

1 >jn\

Figure 11-2: Data entered on the Employees and Salaries form will be transferred to the worksheet.

5. Switch back to the Visual Basic Editor window, and double-click the form background to activate the form module.

6. Enter the following variable declarations at the top of the form Code window:

Option Explicit

Dim emp As New CEmployee

Dim CEmployees As New Collection

Dim index As Integer

Dim ws As Worksheet

Dim extract As String

Dim cell As Range

Dim lastRow As Integer

Dim empLoc As Integer

Dim startRow As Integer

Dim endRow As Integer

Dim choice As Integer

Dim amount As Long

The first statement declares the variable emp as a new instance of the CEmployee class. The second statement declares a custom collection. The CEmployees collection will be used to store employee data. Other variables declared here will be used by VBA procedures assigned to various controls on the form.

7. Type the following UserForm_Initialize procedure to enable or disable controls on the form:

Private Sub UserForm_Initialize() txtLastName.SetFocus cmdEmployeeList.Visible = False lboxPeople.Enabled = False Frame1.Enabled = False txtRaise.Value = "" optPercent.Value = False optAmount.Value = False txtRaise.Enabled = False optPercent.Enabled = False optAmount.Enabled = False

Frame2.Enabled = False optHighlighted.Enabled = False optAll.Enabled = False cmdUpdate.Enabled = False cmdDelete.Enabled = False End Sub

The statements entered inside the UserForm_Initialize procedure will enable only the desired controls when the form is loaded (Figure 11-3).

Figure 11-3:

The UserForm_Initialize procedure disables certain controls that cannot be used when the form is first loaded.

8. Enter the following cmdSave_Click procedure to transfer the data entered on the form to the spreadsheet:

Private Sub cmdSave_Click()

If txtLastName.Value = "" Or txtFirstName.Value = "" Or _ txtSalary.Value = "" Then MsgBox "Enter Last Name, First Name and Salary." txtLastName.SetFocus Exit Sub End If

If Not IsNumeric(txtSalary) Then

MsgBox "You must enter a value for the Salary." txtSalary.SetFocus Exit Sub End If

If txtSalary < 0 Then

MsgBox "Salary cannot be a negative number." Exit Sub End If

Worksheets("Salaries").Select index = ActiveSheet.UsedRange.Rows.Count + 1

lboxPeople.Enabled = True

'set and enter data into the CEmployees collection With emp

Cells(index, 1).Formula = emp.Id .LastName = txtLastName Cells(index, 2).Formula = emp.LastName .FirstName = txtFirstName Cells(index, 3).Formula = emp.FirstName .Salary = CCur(txtSalary) If .Salary = 0 Then Exit Sub Cells(index, 4).Formula = emp.Salary CEmployees.Add emp End With

'delete data from text boxes txtLastName = ""

Figure 11-3:

The UserForm_Initialize procedure disables certain controls that cannot be used when the form is first loaded.

txtFirstName = "" txtSalary = "" 'enable hidden controls cmdEmployeeList.Value = True cmdUpdate.Enabled = True cmdDelete.Enabled = True Framel.Enabled = True txtRaise.Enabled = True optPercent.Enabled = True optAmount.Enabled = True Frame2.Enabled = True optHighlighted.Enabled = True optAll.Enabled = True txtLastName.SetFocus End Sub

The cmdSave_Click procedure starts off with validating the user's input in the Last Name, First Name, and Salary text boxes. If the user entered correct data, VBA assigns to the variable Index the number of the first empty row on the active sheet for data entry purposes. The next statement enables the form's list box control.

When the program reaches the With emp... construct, a new instance of the CEmployee class is created. The LastName, FirstName, and Salary properties are set based on the data entered in the corresponding text boxes, and the Id property is set with the number generated by the statements inside the Class_Initialize event procedure. Each time VBA sees the reference to the instanced emp object, it will call upon the appropriate Property Let procedure located in the class module.

The last section of this chapter demonstrates how to walk through this procedure step by step to see exactly when the property procedures are executed. After setting the object property values, VBA transfers the employee data to the worksheet. The last statement inside the With emp. . . construct adds the user-defined object emp to a custom collection called CEmployees.

Next, Visual Basic removes the current entries from the form's text boxes and enables command buttons that were turned off by the UserForm_Initialize procedure. Notice the first instruction in this block: cmdEmployeeList.Value = True. This statement causes the automatic execution of the cmdEmployeeList_Click procedure attached to the Update List command button (by the way, this is the only control that the user never sees). The code for this procedure is shown below.

9. Type the cmdEmployeeList_Click procedure, as shown here:

Private Sub cmdEmployeeList_Click() lboxPeople.Clear

For Each emp In CEmployees lboxPeople.Addltem emp.Id & ", " & _ emp.LastName & ", " & emp.FirstName & ", $" & _

Format(emp.Salary, "0.00")" Next emp End Sub

The cmdEmployeeList_Click procedure is attached to the Update List command button. This button is controlled by the cmdSave_Click procedure and causes the new employee data to be added to the list box control. The cmdEmployeeList_Click procedure begins with clearing the contents of the list box and then populating it with the items stored in the custom collection CEmployees.

Figure 11-4:

The list box control displays employee data as entered in the custom collection CEmployees.

Figure 11-4:

The list box control displays employee data as entered in the custom collection CEmployees.

10. Type the following cmdClose_Click procedure:

Private Sub cmdClose_Click()

Unload Me End Sub

The cmdClose_Click procedure allows you to remove the user form from the screen and finish working with the custom collection of employees. When you run the form again, the employees you enter will become members of a new CEmployees collection.

11. Type the following cmdDelete_Click procedure: Private Sub cmdDelete_Click()

' make sure that an employee is highlighted in the ' list control

If lboxPeople.ListIndex > -1 Then

MsgBox "Selected item number: " & lboxPeople.ListIndex extract = CEmployees.Item(lboxPeople.ListIndex + 1).Id MsgBox extract

Call FindId MsgBox empLoc

MsgBox "There are " & CEmployees.Count & _ " items in the CEmployees collection. " CEmployees.Remove lboxPeople.ListIndex + 1

MsgBox "The CEmployees collection has now " & _ CEmployees.Count & " items." cmdEmployeeList.Value = True If CEmployees.Count = 0 Then Call UserForm_Initialize End If Else

MsgBox "Click the item you want to remove." End If End Sub

The cmdDelete_Click lets you remove an employee from the CEmployees custom collection. To delete an employee, you must click the appropriate item in the list box. When you click a list item, the cmdEmployeeList_Click procedure is automatically executed. This procedure makes sure that the list box contents are refreshed. The employee is removed both from the collection and from the list box. If the list box contains only one employee, VBA calls the UserForm_Ini-tialize procedure to disable certain form controls after removing the last employee from the collection.

The cmdDelete_Click procedure contains several MsgBox statements, which allow you to examine the contents of the list box control as you make deletions. In addition to removing the employee from the custom collection, the cmdDelete_Click procedure must also remove the corresponding row of employee information from the worksheet. Locating the employee data in the worksheet is handled by the FindId function. (The code of this procedure is in step 12 below.) This function returns to the cmdDelete_Click procedure the row number that has to be deleted.

12. Type the following function procedure: Private Function FindId()

Set ws = ActiveWorkbook.Sheets("Sheet5") startRow = ActiveSheet.UsedRange.Rows.Count + _

1 - CEmployees.Count endRow = ActiveSheet.UsedRange.Rows.Count For Each cell In ws.Range(Cells(startRow, 1), _ Cells(endRow, 1)) If cell.Value = extract Then empLoc = cell.Row FindId = empLoc Exit Function End If

Next End Function

The FindId function procedure returns to the calling procedure the row number that contains the data of the employee who is currently selected in the form's list box. The search for the data in the worksheet is based on the contents of the variable extract that stores the unique employee number. The search for the employee ID is limited to the first worksheet column and begins with the row in which the first collection item was placed. This approach makes the search faster. You don't want to search the entire used area of the worksheet. Recall that if you use the form more than once, the contents of your custom collection will not include the previously entered employees. 13. Type the following cmdUpdate_Click procedure:

Private Sub cmdUpdate_Click()

If optHighlighted = False And optAll = False Then MsgBox "Click the 'Highlighted Employee' or " _

& " 'All Employees' option button." Exit Sub

End If

If Not IsNumeric(txtRaise) Then

MsgBox "This field requires a number."

txtRaise.SetFocus

Exit Sub

End If

If optHighlighted = True And _ lboxPeople.ListIndex = -1 Then

MsgBox "Click the name of the employee." Exit Sub

End If

If lboxPeople.ListIndex <> -1 And _ optHighlighted = True And _ optAmount.Value = True And _ txtRaise.Value <> "" Then extract = CEmployees.Item(lboxPeople.ListIndex + 1).Id

MsgBox extract

Call FindId

MsgBox empLoc choice = 2

amount = txtRaise

CEmployees.Item(lboxPeople.ListIndex + 1).Salary = _

emp.CalcNewSalary(choice, _ CEmployees.Item(lboxPeople.ListIndex + 1).Salary, amount) Range("D" & empLoc).Formula = CEmployees. _

Item(lboxPeople.ListIndex + 1).Salary cmdEmployeeList.Value = True ElseIf lboxPeople.ListIndex <> -1 And _ optHighlighted = True And _ optPercent.Value = True And _ txtRaise.Value <> "" Then extract = CEmployees.Item(lboxPeople.ListIndex + 1).Id MsgBox extract Call FindId MsgBox empLoc

CEmployees.Item(lboxPeople.ListIndex + 1).Salary = _ CEmployees.Item(lboxPeople.ListIndex + 1).Salary + _ (CEmployees.Item(lboxPeople.ListIndex + 1).Salary * _ txtRaise / 100) Range("D" & empLoc).Formula = CEmployees. _

Item(lboxPeople.ListIndex + 1).Salary cmdEmployeeList.Value = True ElseIf optAll = True And _

optPercent.Value = True And _ txtRaise.Value <> "" Then

For Each emp In CEmployees emp.Salary = emp.Salary + ((emp.Salary * txtRaise) _ / 100)

extract = emp.Id MsgBox extract Call FindId MsgBox empLoc

Range("D" & empLoc).Formula = emp.Salary Next emp cmdEmployeeList.Value = True ElseIf optAll = True And _

optAmount.Value = True And _ txtRaise.Value <> "" Then

For Each emp In CEmployees emp.Salary = emp.Salary + txtRaise extract = emp.Id

MsgBox extract

Call FindId

MsgBox empLoc

Range("D" & empLoc).Formula = emp.Salary Next emp cmdEmployeeList.Value = True

Else

MsgBox "Enter data or select an option."

End If End Sub

With the cmdUpdate_Click procedure, you can modify the salary by the specified percentage or amount. The update can be done for the selected employee or all the employees listed in the list box control and collection. The cmdUpdate_Click procedure checks whether the user selected the appropriate option buttons and entered the increase figure in the text box. Depending on which options are specified, the Salary amount is updated for an employee or all the employees, either by the percentage or amount. The salary modification is also reflected in the worksheet. Figure 11-5 displays the salary of James Nolan, which has been increased by 10 percent. By entering a negative number in the text box, you can decrease the salary by the specified percentage or amount.

Figure 11-5:

The employee salary can be increased or decreased by the specified percentage or amount.

Figure 11-5:

The employee salary can be increased or decreased by the specified percentage or amount.

14. Insert a standard module into the current project by choosing Insert | Module. Rename this module WorkAndPay. Type the following procedure to display the Employees and Salaries form:

Sub ClassDemo( ) Salaries.Show

End Sub

15. Run the ClassDemo procedure to work with the custom class.

You can also display the Salaries form by clicking the form's background and pressing F5, or you can place a button in a worksheet and assign the ClassDemo procedure to it (see Chapter 10 for placing a button in a worksheet).

0 0

Post a comment