1. More Events

The Change, Enter and Exit events	Checkbox and Option Button
Practice	Exercise

Assignment

Learning Outcomes

On completion of this chapter you will know:

· The nature of the events, Activate, Change, Enter and Exit

· How those events can be used to automate the running of an application.

Download pdf version

Introduction

In chapter 11 we looked at the Click event of the command button control. In the code this was designated as cmdCalculate_Click. This event occurs when we click on that command button with the mouse. Similarly if we had a button named cmdSave, then the code for the click event of that button would be cmdSave_Click.

The Click event is not restricted to command buttons. Most other objects you place on a form also have a Click event, including labels and textboxes. If you wish to know all of the events that are attached to the controls on your form, then go to the code window and from the left combo box at the top of your code window select the control you wish to examine. From the events list box to the right, click on the down pointing arrow. This will display the list of events attached to the control you selected. This is shown in Figure 12‑1 below.

Figure 12‑1

The Change, Enter and Exit events

Go to top

When browsing the internet or even using a word processor or a spreadsheet we see things appearing and disappearing on the screen the whole time without any effort on our part. These notices and images that appear are caused by events that are attached to objects within the applications we are dealing with or else to the Windows operating system itself.

We wish to make our form as professional looking as possible and also to make it as easy as possible for the user to use the form’s controls correctly. As an example of this, when the user has selected the textbox txtHours in order to enter data into it, we would like some instruction to appear somewhere on the form to instruct the user as to what values for hours he is supposed to enter. On the other hand when the user has left this textbox and has gone to another one we would like this message to disappear – since the message applies only to txtHours. In order to do this we use the Enter and Exit events of the textbox.

The Enter event occurs when we click on the textbox or move into it in any other way. Thus we can put into the procedure attached to this event the code for making the instructions appear.

The Exit event occurs when we leave the textbox and move somewhere else. Again we use code within the procedure attached to this event to remove the instructions.

As well as providing instructions to the user we would also like the form to automatically calculate the pay details for us while we are adjusting the value of either hours or rate. In order to do this we use the Change event of the appropriate textboxes. The Change event of a textbox occurs when the Text property of that textbox changes, in other words when we use any keyboard key while that textbox has the focus. In our case we will put a call to calculatePay into the Change events of the textboxes txtHours and txtRate.

In Listing 12‑1 below we have the code that controls the Change, Enter and Exit events for the textboxes txtHours and txtRate. Lines 1 – 7 control the Change events while lines 9 – 23 control the Enter and Exit events.

Listing 12‑1

1	Private Sub txtHours_Change()
2	calculatePay
3	End Sub
4
5	Private Sub txtRate_Change()
6	calculatePay
7	End Sub
8
9	Private Sub txtHours_Enter()
10	frmPayDemo.Caption = "Value for hours must be in the range 5 - 40"
11	End Sub
12
13	Private Sub txtHours_Exit(ByVal Cancel As MSForms.ReturnBoolean)
14	frmPayDemo.Caption = "Payroll demo - using a Form"
15	End Sub
16
17	Private Sub txtRate_Enter()
18	frmPayDemo.Caption = "Value for rate must be in the range 2 - 10"
19	End Sub
20
21	Private Sub txtrate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
22	frmPayDemo.Caption = "Payroll demo - using a Form"
23	End Sub

Let us now look at the events referred to and see how they control the processing of the payroll. We shall begin with the Change events. In a textbox a change occurs whenever the value of the Text property is changed. Thus adding a single character to the textbox or removing one causes a Change event to occur. As an example entering the value 16 into txtHours causes the Change event to occur twice, once for entering 1 and a second time for entering 6. Each time the Change event of this textbox occurs, the code in lines 1 – 3 is run. This code simply calls the procedure calculatePay – which both calculate the pay and updates the other 3 textboxes.

The exact same logic applies to the code in lines 5 – 7. This group of lines control the Change event of txtRate whenever the user enters data into that textbox.

Before we look at the code for the Enter and Exit events we must first examine the idea of the active control on a form. As a spreadsheet user you are familiar with the concept of the active cell. This is the cell in the worksheet where you can enter data. If you want to enter data into another cell, you must first make that cell the active cell.

The same concept applies to textboxes and other controls on a form. In the form shown in Figure 11‑7 above we have five textboxes. At any time only one of those textboxes can be the active one, i.e. only one of them can receive data from the keyboard. If we want to enter data into any other textbox we must first make that the active textbox, usually by clicking on it using the mouse. If txtHours was the active textbox and if we made txtRate the active textbox by clicking on it with the mouse, then two things would happen, one after the other: the focus would shift from txtHours and the focus would shift to txtRate. This would mean that two events would occur the Exit event of txtHours and the Enter event for txtRate. Again, if we were to shift the focus back to txtHours then two events would occur again: the Exit event for txtRate and the Enter event for txtHours.

As can be seen from the code in lines 9 – 23 above code can also be added to the Enter and Exit events. The code for those events, however, is not involved in processing the payroll data. Instead is it used to help the user use the form correctly. Thus if the hours had to be a number in the range 5 – 40 and the rate had to be in the range 2 – 10, then, when the cursor was in txtHours we should give some indication to the user that the value entered is to be in the range 5 – 40. To do this we attach code the Enter event of the textbox i.e. the event txtHours_Enter or lines 9 – 11 above. The code here alters the Caption property of the form to Value for hours must be in the range 5 – 40. Of course this piece of information must be visible only when the focus is on txtHours. Once the focus shifts to another control then the Caption property of the form must revert to the default value of Payroll demo - using a Form. This is done in the Exit event of txtHours as shown in lines 13 – 15 above.

The code for the Enter and Exit events of txtRate uses the same logic as above.

Figure 12‑2

More Controls – Checkbox, Option Button and Frame

Go to top

Next to textboxes, the next most common controls used to interact with a computer user are checkboxes and option buttons. The checkbox control is used to indicate a true/false or yes/no situation. In the case of our payroll application we may use a checkbox to indicate whether an employee is a member of a trade union or not. We can use this information to decide whether to deduct union fees from his pay or not. In Figure 12‑3 below we have another illustration of the toolbox with the Checkbox, Frame and OptionButton icons highlighted.

Figure 12‑3

Of the properties of a checkbox we shall be using only three: Name, Caption and Value. The Name and Caption properties should be familiar to you by now but the property Value is new. This property is of Boolean type and therefore can only hold values true or false. The property Value is true if the checkbox is checked – i.e. if there is a checkmark inside it - and false if the checkbox is blank.

The option button is somewhat similar to the checkbox, in that it is also used to indicate a true/false situation. Again we shall be using the same three properties: Name, Caption and Value.

Despite their initial similarities, there are fundamental differences between the checkboxes and the option buttons. The main difference is that while the checkboxes are independent of each other, option buttons can be processed only as a group. Thus, in a group of checkboxes any number of boxes can be checked and unchecked. On the other hand in a group of option buttons only one button can be checked at any one time – all other buttons in the group must be unchecked.

It is common to put option buttons inside a frame in order to emphasise the fact that they form a group. If a form has only one group of option buttons then there is no actual need, apart from emphasising clarity, for putting them inside a frame. On the other hand if a form has more than one group of option buttons and each group is meant to be independent of each other then the different groups must be placed in side individual frames.

Apart from separating groups of option buttons from each other, another advantage of putting a number of related objects inside a frame is that if we wish to make the group invisible, we only need to make the frame invisible and everything inside it will also become invisible. The properties of the Frame control that we shall be using are Name, Caption and Visible.

For a practical example of the difference between checkboxes and option buttons we shall look at extending our form application in order to include union fees and superannuation.

An employee may or may not be a union member, i.e. it is a true/false situation. A checkbox can represent this situation for us. This checkbox will have a Name of chkUnion, and a Caption property of Union Member. At the moment those are the only properties of the checkbox that we need to change. The Value property will be altered during the running of the programme, simply be clicking on the checkbox.

Referring to the superannuation situation, the same concept applies to it – either the employee pays superannuation or he doesn’t. Again a checkbox will illustrate this situation for us. The Name and Caption properties of this checkbox will be chkSuper and Superannuation.

The superannuation situation, however, is a little more complex than the union one. With the union, if a person is a union member he pays K10 as a flat fee, while a non-union member pays no fees. On the other hand if an employee is a superannuation member then he has the option of paying either 5%, 10% or 15% of his gross towards his superannuation contributions. Clearly he has to select one of those options and again he can select only one of the options. This situation lends itself perfectly to using option buttons.

For our application we shall have three option buttons whose Name properties will be opt05, opt10 and opt15. Their Caption properties will be 5%, 10% and 15%.

Those option buttons will be inside a Frame control whose Name will be frmSuper and whose Caption will be Super Rate.

Next we look at keeping unnecessary controls off the form. As an example if an employee is not a union member there is no need for the textbox txtUnion to be visible. Similarly if the employee does not pay any superannuation contributions then neither txtSuper or frmSuper should be visible. We manage this by making those three controls invisible at the beginning. Then if the user clicks on either of the checkboxes then the Visible property of the three controls can be turned on or off depending on whether the checkbox in question is checked or unchecked.

These new controls are displayed below in Figure 12‑3, while the code that controls the extended form is shown in Listing 12‑2

Figure 12‑4

Listing 12‑2

1	Sub calculatePay()
2	Dim sngHours As Single
3	Dim curRate As Currency
4	Dim blnUnion As Boolean
5	Dim curUnionFees As Currency
6	Dim blnSuper As Boolean
7	Dim sngSuperRate As Single
8	Dim curSuper As Currency
9	Dim curGross As Currency
10	Dim curTax As Currency
11	Dim curNet As Currency
12	sngHours = Val(txtHours.Text)
13	curRate = Val(txtRate.Text)
14	blnUnion = chkUnion.Value
15	blnSuper = chkSuper.Value
16	If blnUnion Then
17	curUnionFees = 10
18	Else
19	curUnionFees = 0
20	End If
21	If blnSuper Then
22	If opt05.Value Then
23	sngSuperRate = 0.05
24	ElseIf opt10.Value Then
25	sngSuperRate = 0.1
26	ElseIf opt15.Value Then
27	sngSuperRate = 0.15
28	End If
29	Else
30	sngSuperRate = 0
31	End If
32	curGross = sngHours * curRate
33	curTax = curGross * 0.25
34	curSuper = curGross * sngSuperRate
35	curNet = curGross - curTax - curUnionFees - curSuper
36	txtGross.Text = curGross
37	txtTax.Text = curTax
38	txtUnion = curUnionFees
39	txtSuper = curSuper
40	txtNet.Text = curNet
41	End Sub
42
43	Private Sub chkSuper_Click()
44	txtSuper.Visible = chkSuper.Value
45	frmSuperRate.Visible = chkSuper.Value
46	calculatePay
47	End Sub
48
49	Private Sub chkUnion_Click()
50	txtUnion.Visible = chkUnion.Value
51	calculatePay
52	End Sub
53
54	Private Sub opt05_Click()
55	calculatePay
56	End Sub
57
58	Private Sub opt10_Click()
59	calculatePay
60	End Sub
61
62	Private Sub opt15_Click()
63	calculatePay
64	End Sub
65
66	Private Sub txtHours_Change()
67	calculatePay
68	End Sub
69
70	Private Sub txtRate_Change()
71	calculatePay
72	End Sub
73
74	Private Sub txtHours_Enter()
75	frmPayDemo.Caption = "Value for hours must be in the range 5 - 40"
76	End Sub
77
78	Private Sub txtHours_Exit(ByVal Cancel As MSForms.ReturnBoolean)
79	frmPayDemo.Caption = "Payroll demo - using a Form"
80	End Sub
81
82	Private Sub txtRate_Enter()
83	frmPayDemo.Caption = "Value for rate must be in the range 2 - 10"
84	End Sub
85
86	Private Sub txtrate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
87	frmPayDemo.Caption = "Payroll demo - using a Form"
88	End Sub

We shall not go through the code from top to bottom order as we normally do. Instead we shall first look at the new code for controlling the checkboxes and option buttons and then we shall look at the adjusted code for calculating the pay.

We first look at the code for controlling the click event of chkSuper. This code spans lines 43 – 47. At first glance you might wonder what on earth is going on. Firstly chkSuper_Click indicates that this is the piece of code that runs when the user clicks on the check box chkSuper. When you click on a checkbox you alter its appearance. If the checkbox was blank then a tick appears inside it, or, if a tick was already there then it goes blank. As we stated earlier, if a tick appears inside a checkbox then its Value property is true, whereas if it is blank then its Value property is false. Thus clicking on a checkbox alters its Value property between true and false. If the employee we are dealing with is a superannuation contributor then want the textbox txtSuper and the option buttons inside the frame frmSuperRate to be visible whereas if the employee is not a superannuation contributor then we want those controls to be invisible, since they are not needed. If an control’s Visible property is true then the control is visible whereas if it’s Visible property if false then the control is invisible.

Line 44 changes the textbox txtSuper to be either visible or invisible. The way it does this is that it reads the Value property of chkSuper and copies this value into the Visible property of txtSuper. Thus if chkSuper has a tick inside it, its Value property is true. This causes the Visible property of txtSuper to be true as well – which means that txtSuper will be visible.

The same logic applies to line 45.

On the other hand if chkSuper is blank, i.e. if it has no tick inside it then then its Value property is false. This means that the employee does not pay any superannuation and therefore we don’t need either txtSuper or the frame frmSuperRate. For this reason we wish to make them invisible by changing their Visible properties to false.

Here we have an example of the same piece of code performing two opposite actions i.e. it may make two controls either visible or invisible depending on whether a checkbox is either checked or unchecked.

Finally line 46 calls calculatePay. This needs to be done since altering the fact that an employee pays or doesn’t pay superannuation contributions will alter how the pay is calculated.

The same logic applies to the Click event of chkUnion, which spans lines 49 – 52 and for this reason we will not elaborate on it here.

Figure 12‑4 below shows how a form would look when the employee pays neither superannuation or union fees.

The code for the Click events of the three option buttons spans lines 54 – 64. This code here is very simple since selecting any one of those buttons effects only the calculation of the pay and does not affect any other control on the form. For this reason all the click events do is to call the procedure calculatePay.

The code in lines 66 – 86 has already been dealt with, and therefore we will not discuss it here. We need however to look at calculatePay since there are significant changes made to it regarding the calculation of the union fees and superannuation contribution. These changes span lines 14 – 31.

In lines 14 and 15 the Value properties of the checkboxes chkUnion and chkSuper are read into the Boolean variables blnUnion and blnSuper.

Lines 16 – 20 is a simple If..Else construct which puts either zero or 10 into the variable curUnionFees, depending on the value off blnUnion.

Lines 21 – 31 control the calculation of superannuation. This is a more complex structure since it contains nested If constructs. Line 21 tests the value of blnSuper. If its value is true then the nested If that spans lines 22 – 28 is executed. This structure tests the Value property of each of the option buttons to check which one of them has a value of true and updates the value off the variable sngSuperRate accordingly.

If the value of blnSuper is false then lines 22 – 28 are skipped and only line 30 is executed, which puts a value of zero into sngSuperRate.

At line 35 we have a change to the calculation of then net since both union fees and superannuation contributions are now subtracted from it.

Figure 12‑5

Practice

Go to top

Create a form as shown in Figure 12‑3. Name the controls as appropriate. Next copy the code in Listing 12‑2 into the form. Of that listing you should only copy the procedure calculatePay. The rest of the code should be generated from the appropriate events of the controls on the form.

Exercise

Go to top

1. Explain when the Change event occurs

2. Explain how the Enter and Exit events are used here to control instructions regarding what is to be entered into textboxes.

3. The Click event applies only to textboxes! True or false?

4. Sub cmdCalculate_Click. What does the name of this procedure mean?

Assignment Part 10

Go to top

Alter your form from Assignment Part 9 to that shown below. Notice that the textbox txtDiscountCode is missing. This is because the entry of the discount code is now done through the option buttons at the right of the form.

Figure 12‑6

Now the calculation of the sales details are performed on the Change events of the text boxes that hold the amount sold and the unit price and on the Change events of the four option buttons. During those calculations no data is saved to the text file “Sales”. Data is saved to this file only when the Save button is clicked.