Displaying text with the MouseOver event
A common charting question deals with modifying chart tips. A chart tip is the small message that appears next to the mouse pointer when you move the mouse over a chart. The chart tip displays the chart element name and (for series) the value of the data point. The Chart object model does not expose these chart tips, so there is no way to modify them.
To turn chart tips on or off, choose Tools ^ Options. Click the Chart tab and select or unselect the two check boxes in the Chart tips section.
This section describes an alternative to chart tips. Figure 18-15 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.
|
- chartsheet.xls |
l-lnlxl | |||||||||||
|
1 st Quarter Performance | ||||||||||||
|
5,000,000 -4 000 000 ■ |
Region 1, February - $5,545.52 J Two-week sales promotion in |
ts | ||||||||||
|
3,000,000 ■ |
n | |||||||||||
|
,_, | ||||||||||||
|
1,000,000 - | ||||||||||||
|
113 Region 1 ■ Region 2 |
March | |||||||||||
|
m < ► Hl\Chartl L Sheetl / | ||||||||||||
Figure 18-15: A text box displays information about the data point under the mouse pointer.
Figure 18-15: A text box displays information about the data point under the mouse pointer.
The event procedure that follows is located in the code module for the Chart sheet that contains the chart.
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 argl As Long, arg2 As Long Dim NewText As String On Error Resume Next
ActiveChart.GetChartElement X, Y, ElementID, argl, arg2 If ElementId = xlSeries Then
NewText = Sheets("Sheet1").Range("Comments").Offset(arg2, argl)
Else
ActiveChart.Shapes(1).Visible = False End If
If NewText <> ActiveChart.Shapes(1).TextFrame.Text Then ActiveChart.Shapes(1).TextFrame.Text = NewText ActiveChart.Shapes(1).Visible = True End If End Sub
This procedure monitors all mouse movements on the Chart sheet. The mouse coordinates are contained in the X and Y variables, which are passed to the procedure. The Button and Shift arguments are not used in this procedure.
As in the previous example, the key component in this procedure is the GetChartElement method. If ElementId is xlSeries, the mouse pointer is over a series. The NewText variable then is assigned the text in a particular cell. This text contains descriptive information about the data point (see Figure 18-16). If the mouse pointer is not over a series, the text box is hidden. Otherwise, it displays the contents of NewText.
|
EI |
riouseover event - chart sheetxls |
IN* | ||
|
A |
B |
C |
D — | |
|
1 |
Re<|ion 1 |
Region 1 |
— | |
|
2 |
January |
3,245,151 |
1,434,343 | |
|
3 |
February |
5,546,523 |
1,230,783 | |
|
4 |
March |
5,083,204 |
3,224,855 | |
|
5 G |
Comments | |||
|
7 |
Region 1, January = $3,245,151 |
Region 2, January - $1,434,343 | ||
|
Region 1, February -(5,540,523 |
Region 2, February -51,238,789 | |||
|
8 |
Two-week sales promotion in effect | |||
|
Region 1, March = $5,083,204 |
Region 2, March = 53,224,855 | |||
|
L.A. merger took place in week three. | ||||
|
10 | ||||
|
> M \ Chartl J\Sheetl / |
_111 1 | |||
Figure 18-16: Range B7:C9 contains data point information that's displayed in the text box on the chart.
Figure 18-16: Range B7:C9 contains data point information that's displayed in the text box on the chart.
The companion CD-ROM contains this example set up for an embedded chart and for a chart sheet.
Post a comment