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