Hi Guys...

I've searched everywhere for this information and have tried everything I
can think of, and cannot get it to work. Often when I run upon problems like
this (i.e. those with no solution that I can find), you guys are able to help
me. I hope this is one of those times!

I have a scatterplot chart (X-Y) that I generate programatically for a group
of users.

I'd like the users to be able to ascertain additional information about a
point, should they choose to do so. I experimented with DataLabels, but they
cause the chart to become almost unreadable. What I would like to do, if
possible, is change the text that is displayed when you hover over a data
point (i.e. Series "Bonds", Point "6/1/2027" (6/1/2027,4.5)). I would like to
be able to customize this text to something that i want it to say - that is,
just a string of sorts.

Is this possible?

If not, does anyone have any clever ideas for how I might be able to do this?

Any help, or suggestions, are most appreciated.

Thanks in advance, this group is a god-send.

Regards,

David

Hi David,

Thanks for the challenging issue! In my investigation for a solution
I came to the conclusion that it would be really nice if Microsof
product development would employ more consistency in the objec
models…

As you undoubtedly know, in the Forms object model it’s possible t
manipulate the text of “hover-over” tips applied to the controls on
form using the ControlTipText property. Unfortunately, all I coul
find in my research is that you can only activate/deactivate
“similar” property for charts using the Application.ShowChartTipName
or Application.ShowChartTipValues properties, not change the text tha
appears in the tip.

Although it’s not your preference for a solution, in my research
found something in the Microsoft Knowledge Base for creating custo
data labels for the chart data points:
http://support.microsoft.com/default...b;en-us;213750

Additionally, *a GIANT THANKS to Tom Ogilvy & Jon Peltier * for thei
work related to your issue in a previous post on this site (a link i
provided to Jon's website for code samples from this thread):

http://www.excelforum.com/showthread...+poin t+macro

Building upon Tom and Jon’s work, I took a different approach using th
Assistant (that animated ‘paper-clip’ help tool) which I’ve neve
attempted to manipulate with VB code before but thought it might be
viable solution…

Since you didn’t provide much detail on the custom text you would lik
to display (based on data series, based on data point, o
both/neither?) or if the chart data source varies (number of dat
series, number of data points?), -my code will need to be modified fo
your needs- (see *BOLD* code below).

This solution was developed using Excel 2003 on an X-Y scatter chart i
a chart sheet. Copy it to the code page of the chart object in the V
project explorer. It utilizes the Chart_BeforeDoubleClick event, so t
make it run, select a data point in your chart by clicking it onc
(this selects the whole data series), click it again (the cursor shoul
change to something that looks like a plus sign with arrow ends), the
double-click it…

Code
-------------------
Private Sub Chart_BeforeDoubleClick(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)

Dim custText as String

If TypeName(Selection) = "Point" Then
Cancel = True
If Arg2 0 Then
' Turn on the Assistant...
With Assistant
.On = True
.Filename = "F1.acs"
.Visible = True
End With
' Populate Assistant w/data based on selected data point...
With Assistant.NewBalloon
.Button = msoButtonSetCancel
' Assign the selected data series to the Assistant header...
.Heading = "Series: " & Arg1 & " - (" & SeriesCollection(Arg1).Name & ")"
' Assign the selected data point to the Assistant text...
.Text = "Data Point: " & Arg2
' DEFINE CUSTOM TEXT TO DISPLAY BASED ON SELECTION...
' IT CURRENTLY DISPLAYS TEXT BASED ON 2 DATA SERIES W/3 DATA POINTS...
SELECT CASE ARG1 ' - THE SELECTED DATA SERIES
CASE 1
SELECT CASE ARG2 ' - THE SELECTED DATA POINT
CASE 1
CUSTTEXT = \"SERIES 1 DATA POINT 1 CUSTOM TEXT.\"
CASE 2
CUSTTEXT = \"SERIES 1 DATA POINT 2 CUSTOM TEXT.\"
CASE 3
CUSTTEXT = \"SERIES 1 DATA POINT 3 CUSTOM TEXT.\"
CASE ELSE
CUSTTEXT = \"SERIES 1 DATA POINT ??? - CONTACT DAVID FOR HELP.\"
END SELECT
CASE 2
SELECT CASE ARG2
CASE 1
CUSTTEXT = \"SERIES 2 DATA POINT 1 CUSTOM TEXT.\"
CASE 2
CUSTTEXT = \"SERIES 2 DATA POINT 2 CUSTOM TEXT.\"
CASE 3
CUSTTEXT = \"SERIES 2 DATA POINT 3 CUSTOM TEXT.\"
CASE ELSE
CUSTTEXT = \"SERIES 2 DATA POINT ??? - CONTACT DAVID FOR HELP.\"
END SELECT
CASE ELSE
CUSTTEXT = \"SERIES ??? - CONTACT DAVID FOR HELP.\"
END SELEC
' Assign custom text to the Assistant...
.Labels(1).Text = custText
' Display the Assistant...
.Show
End With
' Hide the Assistant after user cancels it...
With Assistant
.On = False
.Visible = False
End With
Else
' Alert user if single data point isn't selected...
MsgBox "Please select a single data point.", vbWarning, "Select Data Point"
End If
End If
End Sub
--------------------

Hope this helps,
theDude


--
theDude
------------------------------------------------------------------------
theDude's Profile: http://www.excelforum.com/member.php...o&userid=16550
View this thread: http://www.excelforum.com/showthread...hreadid=468050

The Dude.

Absolutely no reason for you to thank me, but me to thank you! Your post has
given me quite a bit to think about, and for that I am most thankful.

I did read Tom and John's posts on this newsgroup, but hadn't seen their
posts on the other - which were, as you say, quite useful. I also tried out
Tushar's utitlity (I personally am not a big fan of using add-ins, as I
prefer writing my own code most of the time), but it had some weaknesses I
could not overcome.

In any event, I am going to play around with the code you so kindly provided
me with and will let you know how it goes.

I really appreciate all the effort you no doubt put into finding a solution
for this problem, and I'm sure future people who search this newsgroup will
as well.

All the best, and thanks again.

David

"theDude" wrote:


Hi David,

Thanks for the challenging issue! In my investigation for a solution,
I came to the conclusion that it would be really nice if Microsoft
product development would employ more consistency in the object
models€¦

As you undoubtedly know, in the Forms object model its possible to
manipulate the text of €œhover-over€� tips applied to the controls on a
form using the ControlTipText property. Unfortunately, all I could
find in my research is that you can only activate/deactivate a
€œsimilar€� property for charts using the Application.ShowChartTipNames
or Application.ShowChartTipValues properties, not change the text that
appears in the tip.

Although its not your preference for a solution, in my research I
found something in the Microsoft Knowledge Base for creating custom
data labels for the chart data points:
http://support.microsoft.com/default...b;en-us;213750

Additionally, *a GIANT THANKS to Tom Ogilvy & Jon Peltier * for their
work related to your issue in a previous post on this site (a link is
provided to Jon's website for code samples from this thread):

http://www.excelforum.com/showthread...+poin t+macro

Building upon Tom and Jons work, I took a different approach using the
Assistant (that animated €˜paper-clip help tool) which Ive never
attempted to manipulate with VB code before but thought it might be a
viable solution€¦

Since you didnt provide much detail on the custom text you would like
to display (based on data series, based on data point, or
both/neither?) or if the chart data source varies (number of data
series, number of data points?), -my code will need to be modified for
your needs- (see *BOLD* code below).

This solution was developed using Excel 2003 on an X-Y scatter chart in
a chart sheet. Copy it to the code page of the chart object in the VB
project explorer. It utilizes the Chart_BeforeDoubleClick event, so to
make it run, select a data point in your chart by clicking it once
(this selects the whole data series), click it again (the cursor should
change to something that looks like a plus sign with arrow ends), then
double-click it€¦

Code:
--------------------
Private Sub Chart_BeforeDoubleClick(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)

Dim custText as String

If TypeName(Selection) = "Point" Then
Cancel = True
If Arg2 0 Then
' Turn on the Assistant...
With Assistant
.On = True
.Filename = "F1.acs"
.Visible = True
End With
' Populate Assistant w/data based on selected data point...
With Assistant.NewBalloon
.Button = msoButtonSetCancel
' Assign the selected data series to the Assistant header...
.Heading = "Series: " & Arg1 & " - (" & SeriesCollection(Arg1).Name & ")"
' Assign the selected data point to the Assistant text...
.Text = "Data Point: " & Arg2
' DEFINE CUSTOM TEXT TO DISPLAY BASED ON SELECTION...
' IT CURRENTLY DISPLAYS TEXT BASED ON 2 DATA SERIES W/3 DATA POINTS...
SELECT CASE ARG1 ' - THE SELECTED DATA SERIES
CASE 1
SELECT CASE ARG2 ' - THE SELECTED DATA POINT
CASE 1
CUSTTEXT = \"SERIES 1 DATA POINT 1 CUSTOM TEXT.\"
CASE 2
CUSTTEXT = \"SERIES 1 DATA POINT 2 CUSTOM TEXT.\"
CASE 3
CUSTTEXT = \"SERIES 1 DATA POINT 3 CUSTOM TEXT.\"
CASE ELSE
CUSTTEXT = \"SERIES 1 DATA POINT ??? - CONTACT DAVID FOR HELP.\"
END SELECT
CASE 2
SELECT CASE ARG2
CASE 1
CUSTTEXT = \"SERIES 2 DATA POINT 1 CUSTOM TEXT.\"
CASE 2
CUSTTEXT = \"SERIES 2 DATA POINT 2 CUSTOM TEXT.\"
CASE 3
CUSTTEXT = \"SERIES 2 DATA POINT 3 CUSTOM TEXT.\"
CASE ELSE
CUSTTEXT = \"SERIES 2 DATA POINT ??? - CONTACT DAVID FOR HELP.\"
END SELECT
CASE ELSE
CUSTTEXT = \"SERIES ??? - CONTACT DAVID FOR HELP.\"
END SELECT
' Assign custom text to the Assistant...
.Labels(1).Text = custText
' Display the Assistant...
.Show
End With
' Hide the Assistant after user cancels it...
With Assistant
.On = False
.Visible = False
End With
Else
' Alert user if single data point isn't selected...
MsgBox "Please select a single data point.", vbWarning, "Select Data Point"
End If
End If
End Sub
--------------------

Hope this helps,
theDude


--
theDude
------------------------------------------------------------------------
theDude's Profile: http://www.excelforum.com/member.php...o&userid=16550
View this thread: http://www.excelforum.com/showthread...hreadid=468050