Chart Events

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

As you know, you can create charts in Excel that are embedded in a worksheet or located on a separate chart sheet. In this section, you will learn how to control chart events no matter where you've decided to place your chart. Before you try out selected chart events, perform the following tasks:

1. Open a new Excel workbook and save it as ChartEvents.xls.

2. Enter sample data, as shown in Figure 14-6.

3. Select cells A1:D4, and click the Chart Wizard button on the Standard toolbar.

4. Prepare a column chart, as shown in Figure 14-6, and embed it in a worksheet.

5. Using the same data, create a line chart on a separate chart sheet (see Figure 14-7).

6. Change the name of the chart sheet to Sales Analysis Chart.

The following table lists events for the Chart object. The example procedures demonstrated in this table should be tried on the chart that you've placed on a separate chart sheet (Figure 14-7). Events for a chart embedded in a worksheet require a special setup and are explained later in this chapter.

Figure 14-6:

Column chart embedded in a worksheet

Figure 14-6:

Column chart embedded in a worksheet

Figure 14-7: Line chart placed in a chart sheet

1. In the Visual Basic Editor window, activate the Project Explorer window and open the Microsoft Excel Objects folder.

2. Double-click the chart object labeled Chartl (Sales Analysis Chart).

3. In the Code window, enter the event procedures shown in the table below.

4. Activate the chart sheet and perform the actions that will trigger the event procedures you've written. For example, after clicking the chart title, the Chart_MouseDown and Chart_Select events should be triggered.

Event

Description

Activate

This event occurs when the user activates the chart sheet.

Private Sub Chart Activate()

MsgBox "You've activated the chart sheet." End Sub

Deactivate

This event occurs when the user deactivates the chart sheet.

Private Sub Chart Deactivated

MsgBox "It looks like you want to leave the chart sheet." End Sub

Select

This event occurs when the user selects a chart element.

Private Sub Chart Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long) If Arg1 <> 0 And Arg2 <> 0 Then

MsgBox ElementID & ", " & Arg1 & ", " & Arg2 End If

If ElementID = 4 Then

MsgBox "You've selected the chart title." ElseIf ElementID = 24 Then

MsgBox "You've selected the chart legend." ElseIf ElementID = 12 Then

MsgBox "You've selected the legend key." ElseIf ElementID = 13 Then

MsgBox "You've selected the legend entry." End If End Sub

ElementId returns a constant representing the type of the selected chart element. Arguments Arg1 and Arg2 are used in relation to some chart elements. For example, the chart axis (ElementId = 21), can be specified as Main Axis (Arg1 = 0) or Secondary Axis (Arg1 = 1), while the Axis Type is specified by Arg2, which can be one of the following three values: 0 - Category Axis, 1 - Value Axis, or 3 - Series Axis.

SeriesChange

This event occurs when the user changes the value of a chart data point. The Chart object should be declared in the class module using the WithEvents keyword.

Calculate

This event occurs when the user plots new or changed data on the chart.

Event

Description

Calculate (cont.)

Private Sub Chart Calculate() MsgBox "The data in your spreadsheet has " & vbCrLf & "changed. Your chart has been updated." End Sub

Resize

This event occurs when the user changes the size of the chart. The Chart object should be declared in the class module using the WithEvents keyword.

DragOver

This event occurs when the user drags data over the chart. The Chart object should be declared in the class module using the WithEvents keyword.

DragPlot

This event occurs when the user drags a range of cells over the chart. The Chart object should be declared in the class module using the WithEvents keyword.

BeforeDoubleClick

This event occurs when the user double-clicks the chart.

BeforeRightClick

This event occurs when the user right-clicks the chart.

Private Sub Chart BeforeRightClick(Cancel As Boolean)

Cancel = True End Sub

When you set the Cancel argument to True, the user will not be able to access the shortcut menu within the chart area.

MouseDown

This event occurs when a mouse button is pressed while the pointer is over a chart.

Private Sub Chart MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As

Long) If Button = 1 Then

MsgBox "You pressed the left mouse button. " Elself Button = 2 Then

MsgBox "You pressed the right mouse button. " Else

MsgBox "You pressed the middle mouse button. " End If End Sub

The Button argument determines which mouse button was pressed (MouseDown event) or released (MouseUp event): 1 - left button, 2 - right button, and 4 - middle button. The Shift argument specifies the state of the Shift, Ctrl, and Alt keys: 1 - Shift was selected, 2 - Ctrl was selected, and 4 - Alt was selected. The x, y arguments specify the mouse pointer coordinates.

MouseMove

This event occurs when the position of a mouse pointer changes over a chart.

MouseUp

This event occurs when a mouse button is released while the pointer is over a chart.

Was this article helpful?

0 0

Post a comment