Can I make a chart size increase and decrease?

[tonywatsonhelp]

Hi Everyone,

I'm not sure this is possible, but I was wondering if anyone knows a way I could increase and decrease the size of a chart according to the number of data lines I'm using?

I have a chart just called "Chart6" (its a horizontal bar chart) it is dynamic and increases and decrease in the number of bars show according to the number of weeks chosen,

So if you 2 weeks it show just 2 bars, but if you choose 52 weeks it show 52 bars,

the problem I have is you could choose 300 weeks? and have 300 bars, now trying to create a chart that looks nice that can cater for 2week and also 300 weeks is proving difficult.

So I was wonder if I could have a macro change the chart height according to the number of bars displayed.

I have the data in Sheet2 column B and could easily do a count to show the number if rows in cell C2

so if this cant be done another way maybe we could do a macro the says "Chart height = C2" and take C2 as 1cm (or any size)?

anyway any ideas please help


Tony

 

[Veritan]

Hi Tony. I used the Macro recorder to see what happens whenever I change the height of a chart. The result was that every 1/10th of an inch of height is recorded as 7.2 pixels. So if you wanted, you could do something like this:

ActiveSheet.Shapes("Chart6").Height = 100 + (7.2 * [C2] * 3)

where 100 is an arbitrary base height for the chart (so you don't end up with a 0 height chart) and I am multiplying the value in C2 by 3 to represent 3/10ths of an inch for each bar. Those numbers should of course be adjusted to where you want them for your specific chart. You could put this line of code in the worksheet change event and trigger it whenever the value in C2 is changed, if you felt like it.

 

[tonywatsonhelp]

Brilliant veritan, that's perfect

Thanks

Tony