Worksheet Events

A Worksheet object responds to such events as activating and deactivating the worksheet, calculating a worksheet, making a change to a worksheet, and double-clicking or right-clicking a worksheet. This section discusses some of the events to which the Worksheet object can respond.

Event Name

Activate

Event Description

Example 1

This event occurs when the user activates a sheet.

Dim shtName As String 'declared at the top

' of the module

Private Sub Worksheet Activate() shtName = ActiveSheet.Name Range("B2").Select End Sub

The example procedure selects cell B2 each time the sheet is activated.

■ Example 1 - Try It Out: In the Visual Basic Editor window, activate the Project Explorer window and open the Microsoft Excel Objects folder. Double-click Sheet2 (Sheet2), and type the example procedure in the Sheet2 (Code) window. Next, switch to the Microsoft Excel application window and activate Sheet2. Notice that when Sheet2 is activated, the selection is moved to cell B2.

Event Name

Deactivate

Event Description

Example 2

This event occurs when the user activates a different sheet.

Private Sub Worksheet Deactivate() MsgBox "You deactivated " & shtName & "." & vbCrLf & "You switched to " & ActiveSheet.Name & "."

End Sub

The example procedure displays a message when Sheet2 is deactivated.

■ Example 2 - Try It Out: In the Visual Basic Editor window, activate the Project Explorer window and open the Microsoft Excel Objects folder. Double-click Sheet2 (Sheet2), and type the example procedure in the Sheet2 (Code) window. Next, switch to the Microsoft Excel application window and activate Sheet2. The Worksheet_Activate procedure that you created in Example 1 will run. Excel will select cell B2 and store the name of the worksheet in the shtName global variable declared at the top of the Sheet2 code module. Now click any other sheet in the active workbook and notice that Excel displays the name of the worksheet that you deactivated and the name of the worksheet to which you have switched.

Event Name

SelectionChange

Event Description

Example 3

This event occurs when the user selects a worksheet cell.

Private Sub Worksheet SelectionChange(ByVal

Target As Excel.Range) On Error Resume Next

Set myRange = Intersect(Range("A1:A10"), Target) If Not myRange Is Nothing Then

MsgBox "Data entry or edits are not permitted." End If End Sub

The example procedure displays a message if the user selects any cell in myRange.

■ Example 3 - Try It Out: In the Visual Basic Editor window, activate the Project Explorer window and open the Microsoft Excel Objects folder. Double-click Sheet3 (Sheet3), and type the example procedure in the Sheet3 (Code) window. Next, switch to the Microsoft Excel application window and activate Sheet3. Click on any cell within the specified range A1:A10. Notice that Excel displays a message whenever you click the cell in the restricted area.

Event Name

Change

Event Description

Example 4

This event occurs when the user changes a cell formula.

Private Sub Worksheet Change(ByVal Target As Excel.Range) Application.EnableEvents = False Target = UCase(Target) Columns(Target.Column).AutoFit Application.EnableEvents = True End Sub

The example procedure changes what you type in a cell to uppercase. The column where the target cell is located is then auto-sized.

■ Example 4 - Try It Out: In the Visual Basic Editor window, activate the Project Explorer window and open the Microsoft Excel Objects folder. Double-click Sheet1 (Sheet1), and type the example procedure in the Sheet1 (Code) window. Next, switch to the Microsoft Excel application window and activate Sheet1. Enter any text in any cell. Notice that as soon as you press the Enter key, Excel changes the text you typed to uppercase, and the column is auto-sized.

Event Name

Calculate

Event Description

Example 5

This event occurs when the user recalculates the worksheet.

Private Sub Worksheet Calculate()

MsgBox "The worksheet was recalculated." End Sub

The example procedure displays a message upon recalculation of the worksheet.

■ Example 5 - Try It Out: Add a new sheet to the active workbook. This exercise assumes that Excel will place Sheet4 in your workbook. In cell A2 of Sheet4 enter 1, and in cell B2, enter 2. Enter the following formula in cell C2: = A2+B2. In the Visual Basic Editor window, activate the Project Explorer window and open the Microsoft Excel Objects folder. Double-click Sheet4 (Sheet4) and enter the code of the Worksheet_Calculate event procedure, as shown above. Switch to the Microsoft Excel window and activate Sheet4. Modify the entry in cell B2 by typing any number. Notice that after leaving edit mode, the Worksheet_Calculate event procedure is triggered, and you are presented with a custom message.

Event Name

BeforeDoubleClick

Event Description

Example 6

This event occurs when the user double-clicks a worksheet cell.

Private Sub Worksheet BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Address = Range("$C$9") Then MsgBox "No double-clicking, please." Cancel = True

Event Name

BeforeDoubleClick

MsgBox "You may edit this cell." End If End Sub

The example procedure disallows in-cell editing when the user double-clicks cell C9.

■ Example 6 - Try It Out: In the Visual Basic Editor window, activate the Project Explorer window and open the Microsoft Excel Objects folder. Double-click Sheet2 (Sheet2), and type the example procedure in the Sheet2 (Code) window. Next, switch to the Microsoft Excel application window and activate Sheet2. When you double-click cell C9, the Event procedure cancels the built-in Excel behavior, and the user is not allowed to edit the data inside the cell. However, the user can get around this restriction by clicking on the formula bar or pressing F2. When writing event procedures that restrict access to certain program features, write additional code that disallows any workaround.

Event Name

BeforeRightClick

Event Description

Example 7

This event occurs when the user right-clicks a worksheet cell.

Private Sub Worksheet BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

With Application.ComrrandBars("Cell") .Reset

If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then With .Controls.Add(Type:=msoControlButton, before:=1, temporary:=True) .Caption = "Print... " .OnAction = "PrintMe" End With End If End With End Sub

Sub PrintMe()

Application.Dialogs(xlDialogPrint).Show arg12:=1 End Sub

The example procedure adds a Print option to the cell shortcut menu when the user selects more than one cell on the worksheet.

■ Example 7 - Try It Out: In the Visual Basic Editor window, activate the Project Explorer window and open the Microsoft Excel Objects folder. Double-click Sheet2 (Sheet2), and type the example procedure in the Sheet2 (Code) window. Insert a new module into the current project and enter the PrintMe procedure, as shown above. This procedure is called by the Worksheet_BeforeRightClick event when the user selects the Print option from the shortcut menu. Notice that the Show method of the dialog box is followed by a named argument: arg12: = 1. This argument will display the Print dialog box with the preselected option button Selection in the Print area of the dialog box. After entering both procedures in appropriate modules, switch to the Microsoft Excel application window and activate Sheet2. Right-click on any single cell. Notice that the shortcut menu appears with the default options. Now make another selection, this time including more than one cell and right-click the selected area. You should see the Print... option as the first menu entry. Click the Print option and notice that instead of the default "Print active sheet," the Print dialog displays Print Selection.

Note: Refer to Chapter 10 for more information on working with shortcut menus programmatically. Also, see Figure 10-3 for information on how to locate Excel's built-in argument lists.

Event Name

FollowHyperlink

Event Description

Example 8

This event occurs when the user clicks a hyperlink in a Microsoft Excel worksheet.

Private Sub Worksheet FollowHyperlink(ByVal Target As Hyperlink) Target.AddToFavorites End Sub

The example procedure adds the hyperlink that the user clicked to the list of Favorites in Internet Explorer.

■ Example 8 - Try It Out: In the Visual Basic Editor window, activate the Project Explorer window and open the Microsoft Excel Objects folder. Double-click Sheetl (Sheetl), and type the example procedure in the Sheetl (Code) window. Switch to the Microsoft Excel application window, and enter a web address in any cell. For example, type: www.wordware.com and press Enter. Now, click the hyperlink to activate the web site. When the Internet Explorer window appears, open the Favorites menu and notice the Wordware site address has been added to the menu.

Event Name

PivotTableUpdate

Event Description

Example 9

This event occurs after a PivotTable report is updated on a worksheet.

This is a new event in Excel 2002.

The Target argument specifies the selected PivotTable report.

Private Sub pivTbl PivotTableUpdate(

ByVal Target As PivotTable) MsgBox Target.Name & " report has been updated." & vbCrLf & "The PivotReport is located in cells " & Target.DataBodyRange.Address

End Sub

Event Name

PivotTableUpdate

pivTbl is a variable that references an object of type Worksheet declared using the WithEvents keyword in a class module.

The example procedure displays a message stating the name of the updated PivotTable report and the range address that the report occupies on the worksheet.

■ Example 9 - Try It Out: Open the PivotReport_2.xls file located on the book's companion CD-ROM. Click any cell in the PivotTable area, and click the Refresh button on the PivotTable toolbar. Figures 14-3 and 14-4 illustrate how to set up the event handler for the Pivot-TableUpdate event.

I Microsoft Visual Basic - PivotR eporl_2.Kls - [cIsPivolTbi (Code)]

¿3 File Edit yiew Insert Format Debug Run Tools Add-lns Wridow Help VBAcodePrint

Project VBAProiecl m m i bIT

VBAProject (PtvotReporfc_2,xls)

Q Microsoft Excel Objects @ Sheet 1 (5heetl) ffl] Shcet2 (Sheet2) ®]Sheet3(Sheet3) ÉÛ Sheet4 (PivotTable Report)

ThisWorkbook Modules Modulel <23 Class Modules

Pfopeittes - ehtPivoHb)

IrkPivotTbl ClassModute Alphabetic | categorized 1

"3

IrkPivotTbl ClassModute Alphabetic | categorized 1

|CIass

^ I [initialize

"3

" ihe WithEvents keyword will cause ihe evenls ' associated with !he Worksheet object to be exposed 1VBA will add the variable rrame to the Object drop down list Private With Events pivTbl As Worksheet_

' assign a reference to the object variable in the ' Classjnitialize ever>t procedure Private Sub ClassJnitializeQ

'the initialize event occurs as soon as a new instance 1 of the class is created

" create a new instance of the class in a standard ' module (Modulel)

* set the object variable to point to the active sheet Set pivTbl = Excel.ActiveSheet End Sub_

' create an event procedure for the event you want to trap ' this procedure displays a message after the PivotTable ' report has been updated

Private Sub pivTbl_PwotTableUpdate(ByVal Target As PivotTable) MsgBox Target.Name &_

' report has been updated." &vbCrlJ_ & "The PivotReport is located in cells " &_ Target. Data B o dyRa n ge_ Addres s End Sub „ i fcfi Jj_j

Figure 14-3: You must use a class module to trap the PivotTableUpdate event. The class module can have any valid module name. The object variable name, pivTbl, can be any valid variable name.

Microsoft Visual Basic - PivotReporl_2.xls [Modulel (Code]]

¿$1 File Edit View insert Format Debug Run Tools Add-lns Window Help VBAcodePrint - 5 X

Pioiect VBAProjecl o ~

g VBAProject (PivotReport_2.Hts)^J

Id -Ö Microsoft E*=el Objects ËQ Sheet 1 (Sheet 1) ■ " Sheets (Sheet2) ■j Sheets (sheets) §3} ShMt4 (FivotTaWo Report) 0 ThisWorkbook □ -Ö Modules

E ta Cless Modules clsPivotlbl

Ptopediet - Module*

I Modulel Module Alphabetic j Categorized j

Ptopediet - Module*

I Modulel Module Alphabetic j Categorized j

|(General|

Unit Events

' declare a module-level object variable to point

' to the instance of the class

Private xIPiv As cIsPivotTbl_

1 write a procedure that creates a new instance ' of the class

' you must run this procedure to enable events 'this procedure will trigger the Initialize event

1 procedure in the class module ' the object variable will be loaded into memory ' and ready for events

Public Sub InitEventsO

Set xlPrv = New cIsPivotTbl End Sub

'after youVe run the above procedure, change ' some data in the pivot table source ' click anywhere in the pivot table report and choose ' the Refresh Data button on the Pivot Table toolbar hfl'I I

Figure 14-4: Before you can trap the PivotTableUpdate event, you must set up an instance of your class module in a standard module and assign the Worksheet object to the pivTbl property of the new object.

0 0

Post a comment