Run A Procedure When Rightclicking A Chart

You can create a procedure that runs automatically each time a user right-clicks on a particular chart with the mouse. To create this type of procedure, you need to capture the BeforeRightClick event associated with the appropriate Chart object.

To create a procedure that executes when a user right-clicks a chart, you create a new procedure and add it to the object code module for the particular chart. In fact, all event-handling procedures that you create for monitoring chart events must reside within the appropriate chart object code module to have Excel execute them automatically. To create a procedure that executes when right-clicking a chart, you name the procedure Chart_BeforeRightClick.

Although the procedure resides in a chart object code module, it can access other procedures within the same workbook. Therefore, you can create a

Chart_BeforeRightClick procedure that calls procedures located in another module. The procedures you create only execute for the chart in whose code module you place them. If you want to have the procedure execute for multiple charts, you copy the procedure to each module.

The BeforeRightClick event has one parameter, Cancel, that passes to the event when it triggers. If the Cancel parameter has the default value of False, the right-click event procedure performs after your procedure executes. If you set the value of the Cancel parameter to True, Excel does not perform the default procedure.

Keep in mind, Excel does not perform the BeforeRightClick event if the mouse pointer is over a shape, a toolbar, or a menu bar. See Chapter 14 for more information about working with charts.

RUN A PROCEDURE WHEN RIGHT-CLICKING A CHART

RUN A PROCEDURE WHEN RIGHT-CLICKING A CHART

L-^s] ThisWorkbook 3

L-^s] ThisWorkbook 3

—Ig] 5heet2 (Sheefë Sheet3 (Sheet3) L® ThisWorkbook Fh23 Modules

Möddel Module2 Module3 Module4

Bar5hape ChartType DepthPercent 100 DisplayBlanksAs 1 - xINotPlottef

Bar5hape ChartType DepthPercent 100 DisplayBlanksAs 1 - xINotPlottef

L-y ThisWorkbook 3 j VBAProject (ChaplS-M

3-S Microsoft Excel Object Chartl (Chartl) Sheet 1 (Sheet 1) —0 5heet2 (5heet2) Sheet3 (Sheet3) L® ThisWorkbook l^l-gg Modules

Moduiel -A Module2 Module3 *5J Module4

BeforeRightClick

Calculate Deactivate DragOver DragPiot

^Q In the Projects window, double-click the chart object node for the chart where you want to place the Chart_BeforeRightClick subroutine.

■ The code module opens for the chart object.

L-y ThisWorkbook 3 j VBAProject (ChaplS-M

3-S Microsoft Excel Object Chartl (Chartl) Sheet 1 (Sheet 1) —0 5heet2 (5heet2) Sheet3 (Sheet3) L® ThisWorkbook l^l-gg Modules

Moduiel -A Module2 Module3 *5J Module4

BeforeRightClick

Calculate Deactivate DragOver DragPiot

-0 In the Object box, click □ and then the Chart option.

< In the Procedure box, click 3 and then the BeforeRightClick option.

AUTOMATING PROCEDURES WITH EXCEL EVENTS

Instead of capturing a right-mouse click on the entire chart, you may want to capture a double-click on an individual chart element. For example, you can execute a procedure when the user double-clicks the Chart Area by typing the following code:

TYPE THIS:

Private Sub Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Argl As Long, ByVal Arg2 As Long, Cancel As Boolean)

If ElementID = xlChartArea Then

Call ShowChartData End If

End Sub

RESULT:

This code checks the value of the ElementID parameter to determine what element of the chart was selected.

You can use the following constants to represent the chart element you want to capture.

xlAxis, xlAxisTitle, xlChartArea, xlChartTitle, xlCorners, xlDataLabel, xlDisplayUnitLabel, xlDownBars, xlDropLines, xlErrorBars, xlFloor, xlHiLoLines, xlLegend, xlLegendEntry, xlMajorGridlines, xlMinorGridlines, xlNothing, xlPivotChartDropZone, xlPivotChartFieldButton, xlPlotArea, xlRadarAxisLabels, xlSeries, xlSeriesLines, xlShape, xlTrendline, xlUpBars, xlWalls xlAxis, xlAxisTitle, xlChartArea, xlChartTitle, xlCorners, xlDataLabel, xlDisplayUnitLabel, xlDownBars, xlDropLines, xlErrorBars, xlFloor, xlHiLoLines, xlLegend, xlLegendEntry, xlMajorGridlines, xlMinorGridlines, xlNothing, xlPivotChartDropZone, xlPivotChartFieldButton, xlPlotArea, xlRadarAxisLabels, xlSeries, xlSeriesLines, xlShape, xlTrendline, xlUpBars, xlWalls

Position Chart Cell Vba

Q Type the VBA code to run when the user right-clicks the chart.

Q Right-click the chart.

■ The Visual Basic Editor creates a new Private subroutine named Chart_BeforeRightClick.

Q Type the VBA code to run when the user right-clicks the chart.

Q Right-click the chart.

■ The Chart_BeforeRightClick procedure executes the specified VBA code.

0 -1

Post a comment