Adjusting the Chart area

[Guest]

Greetings all,

Excel adjust the chart functions (Data areas Chart is using) automatically
when I insert the row or a column in the middle of the chart area. Can I
invoke that Excel functionality from the macro without inserting anything?

Best Regards, Mani

 

[MC]

Mr Mani,

Sure! Just modify the series formulas:

ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R7C2:R11C2"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R7C3:R11C3"

where you would simply increse R11 to R12 or C2 to C3.

MC

 

[Guest]

Greetings MC,

I know the modifying of the formulas themselves, but what I am looking for
is an event I could send to Excel "User just inserted columns B:C to sheet X"
and let Excel do the job of modifying every underlying formulas including
defined names.

All this I'd like to do without actually inserting row or a column. I do not
like to copy ready functionality in Excel to my macros. Either I'd like to

1. Be able to send event to Excel for Row/Column changes like Excel
internally (?) does in row/column insert

or

2. Be able to prevent excel from sending the actual row or column insert
event. This is alternative where I would insert a column, disable event and
delete column back.

Changing all those formulas in macro is painful. I have a sheet where I have
macro copying data from one workbook to another. I can not predict the usage
of the charts and names in the destination workbook. I would like to
"automatically" adjust everything in the target workbook.

Best regards,
Mani