Create User Forms in Microsoft Excel-P2

Saturday, 2 October 2010 by nkoknki | 0 comments

Adding the Code: 2 Making the Buttons Work

There are three command buttons on the form and each must be powered by its own procedure. Starting with the simple ones…

Coding the Cancel Button:

Earlier, we used the Properties Window to set the Cancel property of the Cancel button to True. When you set the Cancel property of a command button to True, this has the effect of "clicking" that button when the user presses the Esc key on their keyboard. But this alone will not cause anything to happen to the form. You need to create the code for the click event of the button that will, in this case, close the form. Here's how:

1. With the UserForm open for editing in the Visual Basic Editor, double-click the Cancel button. The form's code window opens with the cmdCancel_Click() procedure ready for editing.

2. The code for closing a form is very simple. Add a line of code to the procedure so it looks like this:

Private Sub cmdCancel_Click()
Unload Me
End Sub

Coding the Clear Form Button:

I added a button to clear the form in case the user wanted to change their mind and reset everything, and to make it easier if they had several bookings to make at one time. All it has to do is run the Initialise procedure again. A macro can be told to run another macro (or series of macros if necessary) by using the Call keyword:

1. Double-click the Clear Form button. The form's code window opens with the cmdClearForm_Click() procedure ready for editing.

2. Add a line of code to the procedure so it looks like this:

Private Sub cmdClearForm_Click()

Call UserForm_Initialize

End Sub

Coding the OK Button:

This is the piece of code that has to do the job of transferring the user's choices and text input on to the worksheet. When we set the Cancel button's Cancel property to True we also set the OK button's Default property to True. This has of clicking the OK button when the user presses the Enter (or Return) key on their keyboard (providing they have not used their Tab key to tab to another button). Here's the code to make the button work:

1. Double-click the OK button. The form's code window opens with the cmdOK_Click() procedure ready for editing.

2. Edit the procedure to add the following code:

Private Sub cmdOK_Click()
    ActiveWorkbook.Sheets("Course Bookings").Activate
    Range("A1").Select
    Do
    If IsEmpty(ActiveCell) = FalseThen
        ActiveCell.Offset(1, 0).Select
    End If
    Loop Until IsEmpty(ActiveCell) = True
    ActiveCell.Value = txtName.Value
    ActiveCell.Offset(0, 1) = txtPhone.Value
    ActiveCell.Offset(0, 2) = cboDepartment.Value
    ActiveCell.Offset(0, 3) = cboCourse.Value
    If optIntroduction = True Then
        ActiveCell.Offset(0, 4).Value = "Intro"
    ElseIf optIntermediate = True Then
        ActiveCell.Offset(0, 4).Value = "Intermed"
    Else
        ActiveCell.Offset(0, 4).Value = "Adv"
    End If
    If chkLunch = True Then
        ActiveCell.Offset(0, 5).Value = "Yes"
    Else
        ActiveCell.Offset(0, 5).Value = "No"
    End If
    If chkVegetarian = True Then
        ActiveCell.Offset(0, 6).Value = "Yes"
    Else
        If chkLunch = False Then
        ActiveCell.Offset(0, 6).Value = ""
        Else
        ActiveCell.Offset(0, 6).Value = "No"
        End If
    End If
    Range("A1").Select
End Sub

How the CmdOK_Click code works:

The first two lines make sure that the correct workbook is active and moves the selection to cell A1:

ActiveWorkbook.Sheets("Course Bookings").Activate

Range("A1").Select

The next few lines moves the selection down the worksheet until it finds an empty cell:

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

The next four lines start to write the contents of the form on to the worksheet, using the active cell (which is in column A) as a reference and moving along the row a cell at a time:

ActiveCell.Value = txtName.Value
ActiveCell.Offset(0, 1) = txtPhone.Value
ActiveCell.Offset(0, 2) = cboDepartment.Value
ActiveCell.Offset(0, 3) = cboCourse.Value

Now we come to the option buttons. These have been placed in a frame on the form so the user can choose only one. An IF statement is used to instruct Excel what to for each option:

If optIntroduction = True Then
    ActiveCell.Offset(0, 4).Value = "Intro"
ElseIf optIntermediate = True Then
    ActiveCell.Offset(0, 4).Value = "Intermed"
Else
    ActiveCell.Offset(0, 4).Value = "Adv"
End If

VBA IF statements are much easier to manage than Excel's IF function. You can have as many options as you want, just insert an additional ElseIf for each one. If there were only two options, you wouldn't need the ElseIf, just the If and Else would suffice (don't forget - they all need an End If).

There is another IF statement for each check box. For the Lunch Required check box, a tick in the box means "Yes" the person requires lunch, and no tick means "No" they don't.

If chkLunch = True Then
ActiveCell.Offset(0, 5).Value = "Yes"
Else
ActiveCell.Offset(0, 5).Value = "No"
End If

We could use a similar IF statement for the Vegetarian check box, but if the person does not require lunch it is irrelevant whether or not they are vegetarian. I any case, it would be wrong to assume that they were not vegetarian simply because they did not require lunch. The IF statement therefore contains a second, nested if statement:

If chkVegetarian = True Then
    ActiveCell.Offset(0, 6).Value = "Yes"
Else
    If chkLunch = False Then
        ActiveCell.Offset(0, 6).Value = ""
    Else
        ActiveCell.Offset(0, 6).Value = "No"
    End If
End If

A tick in the box means "Yes" the person is vegetarian. If there is no tick in the box, the nested IF statement looks at the Lunch Required check box. If the Lunch Required check box has a tick in it then no tick in the Vegetarian check box means that the person is not vegetarian so it inserts "No" into the cell. However, if the Lunch Required check box does not have a tick in it, then we do not know whether or not the person is vegetarian (it doesn't matter anyway) so the cell is left blank ("").

Finally the selection is taken back to the beginning of the worksheet, ready for the next entry:

Range("A1").Select

Adding the Code: 1 Initialising the Form
Adding the Code 3: Manipulating the Form

Source: Exceltips

Excel tips

Connecting IT

Create User Forms in Microsoft Excel-P2

0 comments:

Post a Comment

Learning English

Followers

Visitors