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