Create User Forms in Microsoft Excel-P3

Sunday 3 October 2010 by nkoknki | 0 comments

Adding the Code 3: Manipulating the Form

Finally, an example of how the controls on a form can be manipulated whilst it is in use. When the control properties were set, the Enabled property of the Vegetarian check box was set to False. When a control is not enabled the user cannot enter a value into it, although it can hold a value that was there already, and VBA can add, remove or change the value.

We don't need to know whether or not the person is vegetarian (even if they are!) if they aren't ordering lunch. So, the Vegetarian check box remains disabled unless a tick is placed in the Lunch Required check box. Then the user is free to tick the Vegetarian check box if they want to. If they tick it we will know that they have answered "Yes" and if they don't we know they have answered "No".

We can toggle the Enabled property from False to True by having a procedure that runs automatically whenever the value of the Lunch Required check box changes. Fortunately, more controls have a Change procedure and the one we use here is chkLunch_Change(). We'll use this to enable the Vegetarian check box when the Lunch Required check box is ticked, and disable it when the Lunch Required check box is not ticked.

There's just one more thing we need to do. Supposing someone ticked the Lunch Required check box, and also ticked the Vegetarian check box. Then they changed their mind and removed the tick from the Lunch Required check box. The Vegetarian check box would be disabled but the tick that was put in earlier would remain.

An extra line of code can make sure the tick is removed when the box is disabled. Here's the whole thing:

Private Sub chkLunch_Change()
    If chkLunch = True Then
        chkVegetarian.Enabled = True
    Else
        chkVegetarian.Enabled = False
        chkVegetarian = False
    End If
End Sub

Opening the Form

The form is now ready for use so it needs to be opened with a simple macro. That can be attached to a custom toolbar button, a command button drawn on the worksheet, or any graphic (right click the graphic and choose Assign Macro). If necessary, create a new module for the workbook and add this procedure:

Sub OpenCourseBookingForm()
frmCourseBooking.Show

End Sub

Screenshot // Create User Forms in Microsoft Excel

Adding the Code: 1 Initialising the Form

Adding the Code: 2 Making the Buttons Work

END.

Source: Exceltips

Excel tips

Connecting IT

Create User Forms in Microsoft Excel-P3

Adding the Code 3: Manipulating the Form

Opening the Form

0 comments:

Post a Comment

Learning English

Followers

Visitors