Creating event procedure code

You cannot write event procedure code using a standard code module, because the code resides behind the objects that contain them. To create an event procedure you will need to follow the steps shown below:

1 From the VBE, choose: View > Project Explorer.

2 In the P roject Explorer, you will see a list of all the open workbooks (Figure 10.10). Locate your workbook in the list, and expand that branch. Y ou'll see a folder called Microsoft Excel Objects. Expand this branch.

3 There will be an icon for each worksheet and chart sheets - if any - in your workbook, and an entry called thisWorkbook. To add a thisWorkbook event procedure, or a Worksheet level event procedure, right-click on the icon and choose View Code from the menu.

4 At the top of the module code window, there are two drop-down boxes. In the one on the left, select Workbook and in the one on the right, select the name of the event you want to add. In this example, I have selected the WorkbookOpen event procedure. Excel will automatically insert the event code shell: the Sub Workbook_Open()and End Sub statements.

p Microsoft Visual Basic - SALESMAN iriTEREACE.Kls - [TliisWoi kbook (Code)]

fp File Edit View Insert Format Debug Run Tools Add-Ins Window Help

.=JHli£l p Microsoft Visual Basic - SALESMAN iriTEREACE.Kls - [TliisWoi kbook (Code)]

fp File Edit View Insert Format Debug Run Tools Add-Ins Window Help

I IHftHgixt

ThiiW wôrBjlJt

I IHftHgixt

ThiiW wôrBjlJt

Figure 10.10 Creating an event procedure

The following event procedure (Listing 10.7) prevents the user from closing the active workbook until the v alue in A1of Sheet 1 is non-empty. Notice the event is called Workbook_BeforeClose and the code shell would be inserted by the steps described earlier. The code w orks by checking to see if the v alue in cell A1 is empty using the statement: If Worksheets ("Sheet1").Range ("A1").Value = "" Then. If true, then the Cancel parameter is set to True, cancelling the operation. A message b ox is also displayed informing the user why the workbook has not closed (see Figure10.11).

Listing 10.7 Event procedure code for Workbook_BeforeClose

Private Sub Workbook_BeforeClose (Cancel As Boolean) If Worksheets ("Sheet1").Range ("A1").Value = "" Then Cancel = True

MsgBox "You cannot close until A1 is not empty" End If End Sub

Event Procedure
Figure 10.11 Illustration of Event Procedure Workbook_BeforeClose ()

This example is a variation of the Auto Open sub procedure that was developed in Listing 3.3. A WorkbookOpen event procedure is written that will call the dayWeekMessage () sub as shown in Listing 10.9. This sub has essentially been described in Chapter 3. The Workbook Open event procedure therefore is as shown in Listing 10.8. Notice the only code here is the call to the sub dayWeekMessage.

Listing 10.8 The Workbook_Open event procedure

Private Sub Workbook_Open ()

dayWeekMessage End Sub

Listing 10.9 The dayWeekMessage sub

Sub dayWeekMessage()

'Sub procedure to give a day of the week message and today's date.

Dim dayNum As Integer

Dim theDate As Date theDate = Date dayNum = Weekday(Date)

Select Case dayNum

Case 2: MsgBox "Today is Monday" & theDate Case 3: MsgBox "Today is Tuesday" & theDate Case 4: MsgBox "Today is Wednesday" & theDate Case 5: MsgBox "Today is Thursday" & theDate Case 6: MsgBox "Today is Friday" & theDate Case Else: MsgBox "Happy Weekend" & theDate End Select End Sub

LiiJii.y.UJiiJJW xJ

Today ¡5 Friday20/06/2003

Figure 10.12 Screenshot of Listing 10.8 and 10.9

0 0

Post a comment