draw a line or bar on XY chart and stick words or comments on fixed places

Experts,
I have an XY chart and I won't to be able to do 2 things:
First: add or draw fixed line or bars on the xy chart. for example I wont to draw the line from point 1 to point 2 but non of these points are part of the data range or series and I really don't want to then to be considered as point a just want to draw straight line anywhere on the xy chart and incase the Chart scale has changed the lines will cope with the scale.
 
Add/ stick words or comments  or clip arts or shapes on fixed places/locations on the chart that would move or cope with the chart if the chart scale has changed in which if the drawing was out of scale it will stay out of scale and won't appear.

Have a look at my attached excel file.By the way, I am stil using excel 2003

draw-lines.xls
bsaisuAsked:
Who is Participating?
 
Saqib Husain, SyedEngineerCommented:
I think that you would be best off using data points and not annotation if you need it to be tied to the graph scale.

If someone manages some vba macro there would be too many unforeseen problems.
0
 
bsaisuAuthor Commented:
I already have too many points and series in my original sheet and I don't want to add any more points. Moreover I don't know how can I make the chart to draw a line between to points only and exclude other points in an xy chart. Also The thing is not just adding a line I want to add shapes in certain places as if they are part of the background
0
 
andrewssd3Commented:
Hi bsaisu
I totally agree with ssaqibh on this one.  For the lines you can easily make them additional series on the chart with just two points and this will handle changes of scale etc.  As for text boxes, you can only move them automatically if they are proper data labels tied to a point on the chart - you could again do that - just have a series with two points both the same, make all the lines and markers invisible and put your text in a data label.  I don't see how you could do it with chart events - you could handle the Resize event, but that's way too flaky.

Stuart
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
bsaisuAuthor Commented:
Well guys, the hustle here  are the shapes. can i stick a chape to a point or a text box to a point and make the point invisible?
isn't there any kind of a could that would say place this shape centered on a certain coordinates?
0
 
andrewssd3Commented:
You can do some things with the markers - if you make them big and give them a picture fill you can have clip art, or I guess you could have pictures of the shapes you need.  Look at the Format data series options for marker style and fill.  You could then have a different series for each shape you need, positioned where you want it, with the relevant picture fill.  It's not perfect, but it might work for you.
0
 
pgorodCommented:
Yes, this is possible with VBA.

Please check this link:

http://www.excelforum.com/excel-charting/332249-return-the-location-of-data-point-on-a-chart.html

There you will find an answer by Jon Peltier (an authority on Excel graphs!), he links you to two Google groups answers where he provides code for what you want.

Getting the point coordinates is a bit tricky, but using his sample code you can get there.
0
 
bsaisuAuthor Commented:
no comment
0
 
bsaisuAuthor Commented:
andrewssd3,

I want to be able to click on the label as well in addition to the marker. How can i do it. could you edit the excel file (Chartevents) you have sent me before?
0
 
andrewssd3Commented:
Hi bsaisu

I think you should raise this as a new question, related to the old one - other people can't participate if it's just tacked on to the end of this one. I can post the code ubntil tomorrow anyway

Stuart
0
 
bsaisuAuthor Commented:
Hi andrewssd3,

You are right. I have just raised it as a new question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.