PDA

View Full Version : Dynamic Chart



taporctv
06-20-2007, 04:50 AM
I created a dynamic column bar chart in my workbook. I wanted to know if it is possible to click on one of the individual bars on the graph and execute a VBA code depending on which bar on the graph you clicked?

JonPeltier
06-21-2007, 04:00 PM
You have a bunch of coding to do, because you have to harness the event of clicking on a chart. Here is an article on chart events:

http://www.computorcompanion.com/LPMArticle.asp?ID=221

taporctv
06-21-2007, 05:52 PM
Thanks. Ill see how far I can get with this.

taporctv
06-28-2007, 05:37 AM
Anyone have experience with chart events? I've coded it to know which column in the bar graph the user chooses. I used the site JonPeltier provided and it was very useful. Im having a couple issues with my dynamic chart.

1. Dynamic Charting only works when I say the series is in columns.

2. If the series is in columns, when using chart event select method. It recognizes each column in the bar graph as the same series. I would like to be able to have the series in rows so each column can be recognized as a diff. series.

I've attached my workbook for reference.

JonPeltier
06-28-2007, 06:24 AM
1. You have to use the OFFSET function correctly for it to recognize the rows (although in most cases it's better to work with series in columns). Look up OFFSET in the help files:

=OFFSET(ref, row_offset, col_offset, row_size, col_size)

put the appropriate items into the appropriate slots to define a new range that starts in the right row and column relative to the refernce range, and is the right number of rows and column in size.

2. Using events you can determine which cell contains the data point. When you click on a point you can get the series number and point number, and the corresponding category label and data value. You can parse the series formula to discover which ranges contain the X and Y values, and fint the Nth item in each range corresponding to the Nth point. Going by rows instead of columns may end up distorting the chart.

maryam
06-28-2007, 06:48 AM
Dear Taporcv,
Can you send me how you created a dynamic bar chart on your workbook please.

maryam
06-28-2007, 07:41 AM
http://www.vbaexpress.com/forum/showthread.php?p=100505#post100505
I had this thread previouusely for making a dynamic bar diagram, it works well but if I open another excel file the same thing doesnt work for that file. Is your method better?