Chart Tips

[Daniel Flynn]

Is there anyway to customize, via VBA, a chart's chart tips.

For example, I have a pie chart and when you pass the cursor over each slice
of the pie, a little tooltip pops up giving the name of the slice and the
percent.

I would like to customize this information but have spent hours in the
object brower and cannot find the property. It isn't Caption and it isn't
Text and it isn't Name. I've tried all 3.

TIA

[Vic Eldridge]

Daniel,

Sadly, there is no way to customize Excel's chart tips.
If however, this is something you *must* have, then it is possible
to fashion your own version that displays a textbox at the mouse
cursor position.

Here is something posted by Stephen Bullen that demonstrates
how to display an autoshape at the cursor postion.

> 1. Create an embedded chart on a worksheet (Sheet 1)
> 2. Select the chart and add a small oval to it. Name the oval ovlTarget
> 3. Switch to the VBE and paste the following into a class module called
> clsChtEvents:
>
> Public WithEvents oCht As Chart
>
> Private Sub oCht_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal
> X As Long, ByVal Y As Long)
>
> With oCht.DrawingObjects("ovlTarget")
> .Left = X * 0.75 - .Width \ 2 - oCht.ChartArea.Left
> .Top = Y * 0.75 - .Height \ 2 - oCht.ChartArea.Top
> End With
>
> End Sub
>
> 4. Paste the following into a normal module:
>
> Dim cChartEvent As New clsChtEvents
>
> Sub StartIt()
>
> Set cChartEvent.oCht = Sheet1.ChartObjects(1).Chart
>
> End Sub
>
> 5. Run the StartIt routine.
> 6. Switch back to the worksheet and select the chart. You should find that
> the oval follows the cursor around, staying approximately centred on the
> cursor.
>
> Regards
>
> Stephen Bullen
> Microsoft MVP - Excel
> http://www.BMSLtd.co.uk

You would need to use something like that to display the tip.
You can also use the chart's MouseMove event in conjunction with
the GetChartElement method to work out which Series and/or Point
is underneath the mouse cursor. You could use that information as
an index to a table that holds your chart tip texts.

Here is a link to one of David Hager's EEE newsletters. Towards the
bottom of the page is some code that might do all of the work for you.
http://www.j-walk.com/ss/excel/eee/eee006.txt
(If you copy that code to a brand new workbook, save the workbook first.)
It uses a painful technique of working out the X,Y coordinates without
having to use chart events. I did it that way because of the fact that
chart events can only be available to one chart at a time, but I needed
it to work for several charts at once.
So if you're only displaying one chart, get the X,Y coordinates from the
MouseMove event.

HTH,
v...@ultratrace.com.au

Daniel Flynn <dfl...@naisp.net> wrote in message news:82pgck$ij3$1...@news.kersur.net...