Coding the User Form

We just created a class to handle our data and modified our Excel Utility class to help the cCustSurvey class. Let's put cCustSurvey to work by coding it into our UserForm.

Open the Customer Survey form (UserForm1). Open the code view by clicking the View Code button on the Project Explorer toolbar, as shown in Figure 4-7.

Figure 4-7. The View Code button displays the code window for UserForm1 (selected).

Add the following module-level variables in the UserForm code window:

Private m_oCustSurvey As cCustSurvey Private m_blnSaved As Boolean

The m_oCustSurvey variable will do most of the work for us, and the m_blnSaved variable will store the return value from the m_oCustSurvey object's Save method.

Now let's put our initialization and cleanup code in place. Add the following code to the UserForm's UserForm_Initialize and UserForm_Terminate events:

Private Sub UserForm_Initialize() Set m_oCustSurvey = New cCustSurvey Set m_oCustSurvey.DBWorkSheet = Sheets("Sheet1") m_oCustSurvey.GetNextID lblID.Caption = m_oCustSurvey.ID m_blnSaved = False ClearForm End Sub

Private Sub UserForm_Terminate()

Set m_oCustSurvey = Nothing End Sub

When the form is initialized, we're instantiating our cCustSurvey object. Then we're setting the DBWorksheet property. This is a very important step. This value must be stored right away so the class can determine the next valid ID and so it knows where to store the data it collects. Then we get the next available ID number and display it in a label. We then initialize our save success flag to False, and call a function to clear the form.

The ClearForm procedure does nothing more than blank out the text input fields and set the check boxes values to False (or not checked).

Private Sub ClearForm() Me.txtPhone.Value = "" Me.txtState.Value = "" Me.chkHeard.Value = False Me.chkInterested.Value = False Me.chkFollowup.Value = False End Sub

Our form has three command buttons: one to save the data entered (Save), one to clear the form and add a new record (New), and one to cancel the data entry operation and close the form without saving the data (Cancel).

The Save button should perform a few functions for us:

• Sending the data to the cCustSurvey class

• Validating the data and returning a message if the data is not valid

• Saving the data if valid and returning a message if the save is successful

• Cleaning up the form after the save and resetting the saved flag

Here is the code for the Save button:

Private Sub cmdSave_Click() With m_oCustSurvey

.State = txtState.Text .PhoneNumber = txtPhone.Text .HeardOfProduct = chkHeard.Value .WantsProduct = chkInterested.Value .Followup = chkFollowup.Value End With

If Not m_oCustSurvey.ValidateData Then MsgBox "State and Phone Number required", vbOKOnly, "Cannot Save" Exit Sub Else m_blnSaved = m_oCustSurvey.Save End If

DoAfterSave m_blnSaved End Sub

The first section of the code is sending the values to the class. In the real world, our class would perform some input validations (such as validating that we entered a phone number using the correct format).

With m_oCustSurvey

.State = txtState.Text .PhoneNumber = txtPhone.Text .HeardOfProduct = chkHeard.Value .WantsProduct = chkInterested.Value .Followup = chkFollowup.Value End With

The second section of the code is calling the m_oCustSurvey.ValidateData method and displaying a message if both text fields do not contain data. If the data is present, the m_oCustSurvey.Save method is called.

Finally, we're calling a function called DoAfterSave to perform our cleanup. We're passing in our success flag so that this method will be the one calling out any messages to the user.

Private Sub DoAfterSave(success As Boolean) If success Then ClearForm lblID.Caption = m_oCustSurvey.GetNextID MsgBox "Record Saved" Else

MsgBox "Could not save record" End If m_blnSaved = False 'resetting flag End Sub

Our cleanup code clears the form, gets the next available ID number from the database, and sends the user a success (or failure) message.

The New command button has the job of clearing the form and getting a new ID from the database. Before it does that, it must check the text fields to see if they have any data entered. The code for the New command button follows:

Private Sub cmdNew_Click() 'sets form up for a new record Dim iAnswer As Integer

'check that current record is saved (if any) If Not m_blnSaved Then 'see if any text data is entered that is not saved If (Len(Me.txtPhone.Value & "") + Len(Me.txtState.Value & "")) <> 0 Then iAnswer = MsgBox("There is unsaved data. Do you want to continue?", _

vbYesNo, "Unsaved Data") If iAnswer = vbYes Then

ClearForm End If Else

ClearForm End If End If End Sub

We're using the following line of code to determine whether we have data in one of our two text input fields:

If (Len(Me.txtPhone.Value & "") + Len(Me.txtState.Value & "")) <> 0 Then

Once again, we use the Len function to help us make this determination. If the length of both strings summed together is greater than 0, then at least one of the fields contains data. If the result is True, then we prompt the user as to whether they want to continue with the new record and throw out the existing data.

The Cancel command button has a very simple job: clearing the form and closing it. Here is the code for the Cancel button:

Private Sub cmdCancel_Click() ClearForm Unload UserForm1 End Sub

The last step is to create a procedure in a standard module to launch our Customer Survey form. In the VBE, add a new standard code module and create a new subroutine named ShowForm. Add the following line of code to the procedure.

Sub ShowForm()

UserForm1.Show End Sub

If you renamed your UserForm object, use that name in place of UserForm1. Let's run our form and enter some data. In Excel, run the ShowForm macro, as shown in Figure 4-8.

Figure 4-8. Running the ShowForm macro

The Customer Survey form displays. The ID displayed is 103, and as you can see in Figure 4-9, the last entry in the table is 102. Also note that row 5 is the next row available for data.

Let's check our code. Enter NY in the State text box, but leave the phone number field blank, and then click the Save button.

Figure 4-10 shows that our cCustSurvey's ValidateData method finds that neither piece of required data is present, and returns a message to our client code in the UserForm. Click OK and enter a phone number, and then check one or more of the check boxes. Click Save.

Userform Excel Erstellen
Figure 4-9. The UserForm showing the next ID available
Figure 4-10. cCustSurvey class validation result

The success message, shown in Figure 4-11, is displayed to the user, and row 5 now contains the data we entered on our UserForm. Notice that the ID label has been updated to show the ID for the next record.

Figure 4-11. Success message and new record displayed

Let's test the New button before we move on to a more advanced UserForm. Clicking the New button with a blank or empty screen does nothing. If check boxes are checked but the text fields are empty, it will clear the screen without a prompt (this is because only the text fields are required). Enter a state and/or phone number and click the New button, and you'll receive a warning that the data has not been saved (as shown in Figure 4-12).

Excel Vba Message Box Without Header
Figure 4-12. Warning the user about unsaved data

Clicking No returns the user to the form without making any changes. Clicking Yes will clear the form for new entry.

We made a useful addition to our cExcelUtils class. Let's export that file (overwriting the existing copy) so we can use that new functionality in other projects.

1. In the VBE Project Explorer, right-click any item in the project tree.

2. Choose Export File, as shown in Figure 4-13.

Figure 4-13. Exporting a module

3. Navigate to wherever you store your *.cls files, and save cExcelUtils.cls.

Was this article helpful?

+1 0

Post a comment