Get source range of histogram.

[MoshiM]

I have a project where users are able to change the source worksheet data based on a combobox. I allow them to create their own charts and place it on a designated worksheet so that the combobox can interact with it. I then use the series .formula property to retrieve which column their chart is referencing. However, the .formula property isn't available and results in an error for certain charts like the histogram. How can I retrieve the source range for charts like these?

 

[joeu2004]

I am not really knowlegeable about this. But I used a combination of Record Macro and exploring the Object Browser in VBA (pressing the f2 and f1 function keys) to try to get some insight.

That might help you to discover the answer on your own.

That said, in Excel 2010 VBA (7.0), I stumbled across a contradiction -- or at least a conundrum.

Record Macro suggests that the following should work:

ActiveChart.SeriesCollection(1).Values -- the y-range
ActiveChart.SeriesCollection(1).XValues -- the x-range

But neither worked for getting (displaying) the ranges.

Hope this helps, if only a little.