Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

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
0
bsaisu
Asked:
bsaisu
3 Solutions
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now