Embedded Chart Events

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

To capture events raised by the chart embedded in a worksheet, you must first create a new object in the class module using the keyword WithEvents. To see how this is done, let's follow the steps outlined below:

1. Activate the Visual Basic Editor window.

2. In the Project Explorer, select VBAProject(ChartEvents.xls).

3. Choose Insert | Class Module.

4. In the Class Modules folder, you will see a module named Class1.

5. In the Properties window, rename Class1 to clsChart.

6. In the Code window of the class module, declare an object variable that will represent the events generated by the Chart object:

Public WithEvents xlChart As Excel.Chart

The Public keyword will make the object variable xlChart available to all modules in the current VBA project. Declaring an object variable using the WithEvents keyword exposes all of the events defined for that particular object type.

After typing in the above declaration, the xlChart object variable is added to the drop-down Object list in the upper-left corner of the Code window, and the events associated with this object variable appear in the Procedure drop-down list box in the upper-right corner of the Code window.

7. Open the Object drop-down list box, and select the name of the xlChart variable. The Code window should now show the skeleton of the xlChart_Activate event procedure:

Private Sub xlChart_Activate() End Sub

8. Add your VBA code to the event procedure. In this exercise, we will add a statement to display a message box. After adding this statement, your VBA procedure should look like the following:

Private Sub xlChart_Activate()

MsgBox "You've activated a chart embedded in " & _ ActiveSheet.Name

End Sub

After typing in the event procedure, you still need to inform Visual Basic that you are planning on using it.

9. In the Project Explorer window, double-click the object named ThisWorkbook, and enter in the [ThisWorkbook (Code)] window the statement to create a new instance of the class named clsChart:

Dim myChart As New clsChart

The instruction shown above declares an object variable named myChart. This variable will refer to the xlChart object located in the class module clsChart. The New keyword tells Visual Basic to create a new instance of the specified object.

10. Enter the following procedure in the [ThisWorkbook (Code)] window to initialize the object variable myChart:

Sub InitializeChart()

' connect the class module and its objects with the Chart object Set myChart.xlChart = _


End Sub

11. Run the InitializeChart procedure. After running this procedure, the event procedures entered in the class module will be triggered in response to a particular event.

12. Activate the Microsoft Excel application window and click the embedded chart. At this time, the xlChart_Activate event procedure that you entered in step 7 should be triggered.

13. You can now enter in the class module additional event procedures for the embedded chart.

Was this article helpful?

0 0

Post a comment