Make bars on a bar chart clickable?
Is there a way to make the bars on a bar chart clickable. I can get the
functionality by coverin g them with an invisible shape but the problem is that the size of the graph changes with changed data and so the covers don't fit over the bars when it rescales. Another alternative I have thought could work is to somehow measure the distance between vertical gridlines and then draw a shape to the psecified dimension and align it to the x axis - pretty complicated don't know if that could be done. Any ideas much appreciated, kind regards, Mark |
Hi Mark,
Can you not adapt the chart event codes you already have from your previous posts? If you go with the invisible covering shape why not simply cover the whole chart and then work out what is under the mouse rather than having multiple covers. Cheers Andy Mark Stephens wrote: Is there a way to make the bars on a bar chart clickable. I can get the functionality by coverin g them with an invisible shape but the problem is that the size of the graph changes with changed data and so the covers don't fit over the bars when it rescales. Another alternative I have thought could work is to somehow measure the distance between vertical gridlines and then draw a shape to the psecified dimension and align it to the x axis - pretty complicated don't know if that could be done. Any ideas much appreciated, kind regards, Mark -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
Mark,
Judging from what you've described, you might have already gone through this process. In any event I've documented the process of creating clickable bars - maybe it can help. http://www.pdbook.com/index.php/exce...ble_bar_chart/ -- Regards, John Mansfield http://www.pdbook.com "Mark Stephens" wrote: Is there a way to make the bars on a bar chart clickable. I can get the functionality by coverin g them with an invisible shape but the problem is that the size of the graph changes with changed data and so the covers don't fit over the bars when it rescales. Another alternative I have thought could work is to somehow measure the distance between vertical gridlines and then draw a shape to the psecified dimension and align it to the x axis - pretty complicated don't know if that could be done. Any ideas much appreciated, kind regards, Mark |
Mark -
You can use the techniques in this article to capture chart events, such as clicking on a bar in a bar chart: http://www.computorcompanion.com/LPMArticle.asp?ID=221 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Mark Stephens wrote: Is there a way to make the bars on a bar chart clickable. I can get the functionality by coverin g them with an invisible shape but the problem is that the size of the graph changes with changed data and so the covers don't fit over the bars when it rescales. Another alternative I have thought could work is to somehow measure the distance between vertical gridlines and then draw a shape to the psecified dimension and align it to the x axis - pretty complicated don't know if that could be done. Any ideas much appreciated, kind regards, Mark |
Hi Andy,
Sorry for the belated reply, that's a great idea, but if I go with covering the bars will I still be able to detect wherre I am in relatyion to them (or I could program the coordinates in depending upon the number of bars in the chart I guess). Thanks for the idea. Mark "Andy Pope" wrote in message ... Hi Mark, Can you not adapt the chart event codes you already have from your previous posts? If you go with the invisible covering shape why not simply cover the whole chart and then work out what is under the mouse rather than having multiple covers. Cheers Andy Mark Stephens wrote: Is there a way to make the bars on a bar chart clickable. I can get the functionality by coverin g them with an invisible shape but the problem is that the size of the graph changes with changed data and so the covers don't fit over the bars when it rescales. Another alternative I have thought could work is to somehow measure the distance between vertical gridlines and then draw a shape to the psecified dimension and align it to the x axis - pretty complicated don't know if that could be done. Any ideas much appreciated, kind regards, Mark -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
Mark -
The technique described in this article: http://www.computorcompanion.com/LPMArticle.asp?ID=221 show how to determine which point was clicked on. No need for "knowing" the coordinates, because the click event provides those, and the GetChartElement uses them to find what you want. The idea of having to keep track of rectangles covering bars in a chart, in which the numbers of bars may change, the axes may recalculate, or the chart itself may move, is giving me a headache. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Mark Stephens wrote: Hi Andy, Sorry for the belated reply, that's a great idea, but if I go with covering the bars will I still be able to detect wherre I am in relatyion to them (or I could program the coordinates in depending upon the number of bars in the chart I guess). Thanks for the idea. Mark "Andy Pope" wrote in message ... Hi Mark, Can you not adapt the chart event codes you already have from your previous posts? If you go with the invisible covering shape why not simply cover the whole chart and then work out what is under the mouse rather than having multiple covers. Cheers Andy Mark Stephens wrote: Is there a way to make the bars on a bar chart clickable. I can get the functionality by coverin g them with an invisible shape but the problem is that the size of the graph changes with changed data and so the covers don't fit over the bars when it rescales. Another alternative I have thought could work is to somehow measure the distance between vertical gridlines and then draw a shape to the psecified dimension and align it to the x axis - pretty complicated don't know if that could be done. Any ideas much appreciated, kind regards, Mark -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
All times are GMT +1. The time now is 08:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com