MyCompany HR Wizard

Personal j

First Name Mid Init Last Name

Date of Birth SSN

Department Job Title

1

!

Customer Relations

Graphics/Creative

Human Resources

Information Services

Marketing

Research

Sales

Figure 4-31. Department combo box bound to named range 3. Stop running the form by clicking the X button.

Adding Navigation to the Form

Our navigation buttons have the task of moving us from step to step in our wizard application. But they also need the ability to put the data from each screen into its place in the UserForm's cPerson object.

Add the following code to the cmdNext_Click event:

Private Sub cmdNext_Click() Dim iNext As Integer StoreData iNext = m_oWizard.NextPage

Me.MultiPage1.Value = m_colSteps(CStr(iNext)).Order - 1 Me.MultiPage1.Pages((m_colSteps(CStr(iNext)).Page) - 1).Caption =

m_colSteps(CStr(iNext)).Caption

ShowNextPage "up" End Sub

The first thing we need to do before we move to the next step in the wizard is retain the values entered on the current form. The StoreData method determines which step the user is on and calls the correct store method based on that location, as shown in Listing 4-4.

Listing 4-4. The StoreData Method Calls the Correct Method for Each Step in the Wizard.

Private Sub StoreData() Select Case m_oWizard.CurrentPage Case 1

StorePerson Case 2

StoreAddress Case 3

StoreEquipment Case 4

StoreAccess End Select End Sub

The code for the store method follows:

Private Sub StorePerson() With m_oEmployee

.FName = Me.txtFname.Value .MidInit = Me.txtMidInit.Value .LName = Me.txtLname.Value If Len(Me.txtDOB.Value & "") > 0 Then

.DOB = Me.txtDOB.Value End If

.SSN = Me.txtSSN.Value .Department = Me.cboDept.Text .JobTitle = Me.txtJobTitle.Value .Email = Me.txtEmail.Value End With End Sub

Private Sub StoreAddress() With m_oEmployee.Address .StreetAddress = Me.txtStreedAddr.Value .StreetAddress2 = Me.txtStreetAddr2.Value .City = Me.txtCity.Value .State = Me.txtState.Value .ZipCode = Me.txtZip.Value .PhoneNumber = Me.txtPhone.Value .CellPhone = Me.txtCell.Value End With End Sub

Private Sub StoreEquipment() Dim opt As MSForms.OptionButton With m_oEmployee.Equipment

For Each opt In Me.fraPCType.Controls If opt.Value = True Then .PCType = opt.Caption Exit For End If Next

For Each opt In Me.fraPhoneType.Controls If opt.Value = True Then .PhoneType = opt.Caption Exit For End If Next

.Location = Me.cboLocation.Text

If Me.chkFaxYN = True Then

Private Sub StoreAccess() Dim opt As MSForms.OptionButton

With m_oEmployee.Access

If Len(Me.cboNetworkLvl.Text & "") > 0 Then

.NetworkLevel = CInt(Me.cboNetworkLvl.Text) End If

.ParkingSpot = Me.cboParkingSpot.Text .RemoteYN = Me.cboRemoteAccess.Text For Each opt In Me.fraBuilding.Controls If opt.Value = True Then .Building = opt.Caption Exit For End If Next End With End Sub

This code simply takes the data from the screen and holds it in the corresponding object within cPerson.

Next, we determine what the next page should be (remember that the MultiPage Pages collection is zero-based, so we're subtracting 1 from our Order property to get the value of the next page).

iNext = m_oWizard.NextPage

Me.MultiPage1.Value = m_colSteps(CStr(iNext)).Order - 1 Me.MultiPage1.Pages((m_colSteps(CStr(iNext)).Page) - 1).Caption =

m_colSteps(CStr(iNext)).Caption

Then we call the ShowNextPage method, telling it which way we want to move:

ShowNextPage "up"

The ShowNextPage method looks like this:

Private Sub ShowNextPage(Direction As String) Dim iCurrPage As Integer Dim iUpDown As Integer iCurrPage = MultiPage1.Value If LCase(Direction) = "up" Then iUpDown = 1 Else iUpDown = -1 End If

MultiPage1.Pages(iCurrPage + iUpDown).Visible = True MultiPage1.Pages(iCurrPage).Visible = False End Sub

This method simply looks at the value of our CurrentPage property and adds or subtracts 1 based upon the Direction argument that is passed into the method.

The cmdPrevious button's Click event looks very similar:

Private Sub cmdPrevious_Click() Dim iPrevious As Integer StoreData iPrevious = m_oWizard.PreviousPage

Me.MultiPage1.Value = m_colSteps(CStr(iPrevious)).Order - 1 Me.MultiPage1.Pages((m_colSteps(CStr(iPrevious)).Page) - 1).Caption =

m_colSteps(CStr(iPrevious)).Caption

ShowNextPage "down" End Sub

The only difference is that we are passing the keyword down to the ShowNextPage method so that we move the user in the proper direction.

Let's add one last event handler to assist us with our navigation. Whenever we change pages on a MultiPage control, the control's Change event fires. We'll use that event to grab the value of the current page and store it in our m_oWizard object's CurrentPage property. Add the following code to the MultiPage1 control's Change event:

Private Sub MultiPage1_Change()

m_oWizard.CurrentPage = MultiPage1.Value + 1 End Sub

Now that we have our navigation working, let's give it a try:

1. With the UserForm open in Design view, click the Run button on the Standard toolbar or press the F5 key.

2. Once the form is open, click the Next Button to move to the second step in our wizard, as defined on our configuration worksheet. This should be the Address screen. Notice that both navigation buttons are now enabled, as shown in Figure 4-32.

3. Click the Previous button to navigate back to the Personal screen, and the Previous button will no longer be active.

4. Click the Next button until you are at the last screen as defined on our configuration worksheet. This should be the Network Access screen. The Next button will no longer be enabled, as shown in Figure 4-33.

Stop the form by clicking the X button.

Figure 4-32. Both navigation buttons are enabled.

Figure 4-33. The Next button is disabled on the last screen in the wizard. Saving the Employee Record

We've done a lot of work so far, and we've got some pretty neat functionality provided to our UserForm from our custom objects. The only thing missing is saving the data to the EmpData worksheet.

Figure 4-33. The Next button is disabled on the last screen in the wizard. Saving the Employee Record

We've done a lot of work so far, and we've got some pretty neat functionality provided to our UserForm from our custom objects. The only thing missing is saving the data to the EmpData worksheet.

Normally, we might create a subroutine, name it something like SaveData(), and call it from our cmdSave_Click event—but our cHRData class already has a SaveEmployee method. We can call that directly from cmdSave_Click with no need to create a save function on our form. Insert the following code in the cmdSave_Click event:

Private Sub cmdSave_Click() Dim oHRData As cHRData

Set oHRData.Worksheet = Sheets("EmpData") oHRData.SaveEmployee m_oEmployee

Set oHRData = Nothing End Sub

After setting the Worksheet property so that our cHRData object knows where to save the data, we call the SaveEmployee method, passing in our m_oEmployee object, which contains all the data to save.

Cleaning Up

We've almost got a complete application finished. Let's finish off by adding code to our Cancel button and putting some cleanup code in our form's Terminate event. Add the following line of code to the cmdCancel button's Click event:

Private Sub cmdCancel_Click()

Unload Me End Sub

This single line of code simply unloads the form without saving any values. Now we'll clean up the objects used by our HRWizard UserForm. Add the following to the UserForm_Terminate event handler:

Private Sub UserForm_Terminate() Set m_oEmployee = Nothing Set m_oLM = Nothing Set m_oWizard = Nothing End Sub

Now let's add a simple function to our project to open the Wizard form. In the VBE, add a standard module. Add the following method to the standard module:

Sub StartWizard()

HRWizard.Show End Sub

This single line of code will display the UserForm when it is run. Testing the HRWizard Application

It's time to take our wizard for a test ride. Let's enter some data into each screen of the wizard and save it to the EmpData worksheet.

From the Excel workbook, run the StartWizard subroutine from the Macro dialog box, as shown in Figure 4-34.

Figure 4-34. Running the StartWizard macro

Figures 4-35 to 4-39 show some sample input values and the saved data on the EmpData worksheet.

Figure 4-35. Personal information added
Figure 4-36. Address information added
Figure 4-37. Equipment information added
Figure 4-38. Access level information added

Figure 4-39. New employee data added to table

Figure 4-39. New employee data added to table

0 0

Post a comment