Microsoft Office Tutorials and References
In Depth Information
Discovering VBA Charting Tricks
Figure 18-18: After converting a chart to a picture, you can manipulate it by using a variety of commands.
To turn chart tips on or off, choose File➜Options to display the Excel Options dialog
box. Click the Advanced tab and locate the Display section. The options are labeled
Show Chart Element Names on Hover and Show Data Point Values on Hover.
This section describes an alternative to chart tips. Figure 18-19 shows a column chart that uses
the MouseOver event. When the mouse pointer is positioned over a column, the text box (a
Shape object) in the upper-left displays information about the data point. The information is
stored in a range and can consist of anything you like.
The event procedure that follows is located in the code module for the Chart sheet that contains
Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, _
ByVal X As Long, ByVal Y As Long)
Dim ElementId As Long
Dim arg1 As Long, arg2 As Long
On Error Resume Next
ActiveChart.GetChartElement X, Y, ElementId, arg1, arg2
If ElementId = xlSeries Then
ActiveChart.Shapes(1).Visible = msoCTrue
ActiveChart.Shapes(1).TextFrame.Characters.Text = _
ActiveChart.Shapes(1).Visible = msoFalse