How to prevent Excel 2003 chart resizing when get new data

[Ria]

Hi all,
I am struggling with excel 2003. I got data, after all calculation it plot charts through named ranges. And Axes values on chart are updated automatically through vba macros. Problem starts when get new data and if Y-axes values change (e.g. from 3 digit to 4 digit or 4 digit to 3 digit, even minius sign with value or removing minus sign with value) then PLOT AREA in chart expands and shrinks horizontally (chart area stays same). I left enough room between chart area and plot area for maximum number of digits for Y-axes value but it still behave nasty. It is frustrating because I have 3 charts from top to bottom to compare data but when one gets shirinks/expands then it disbalances the appearance of other charts and data display. Any solution through vba macro or within excel 2003 would be appreciated.

Thanks all for any possible help.

Riaz

 

[Hui]

Ria

Can you post a sample file?

 

[Ria]

[
Thanks for looking at this problem. I just got alert message on forum but can not see your message on forum, then saw your message in my email. That's why sending you files through email.
Attached are 2 text files for sample data and one excel 2003 workbook. Put anywhere these sample files on your computer.
Problem is chart shrinks/expands horizontally when data changes.
Open excel file, look at plot area on left side of plot area (along scale of y axes). e.g. Chart area is set from column Z to AQ and plot area is set to start from column AB. Lower chart shrinks/expands on left side when data changes.
In colum AF-AH/rows 50-53 (red highlighted), click on command button SOURCE FILE/FOLDER and select file path of text files folder. Look at current left alignment of plot area of both charts. In column AJ/row 33, click on command buttton IMPORT A/D. Look at left alignment of plot area of both charts, chart area of lower chart has shrinked on new/different data. To get back preious view, enter SPXD in text box AC31 (yellow highlighted) then click command button IMPORT DATA (located in column AC/row 33. Right now this is best I can describe. I have VBA/EXCEL basic level knowledge and learning as I go. Spent month or so surfing web to find solution and now turning to you guys for help. Let me know if anything not clear then I will try to explain it that make sense.
Thanks, Riaz

 

[Ria]

Ria

Can you post a sample file?

Hi Hui:
On lower chart, values on y-axis causing the chart shrink/expand (on new data if number of digits on y-axis get increased or decreased e.g. 123,123,123 changes to 6 digits then chart expands and if changes to 12 or 15 digits then chart shrinks). After chart is updated with new data, if somehow we can manage to display y-axis value to constant number of digits then charts will not expand/shrinks (e.g. 123M =>million or so) but I could not find this solution. I even tried to delete value of y-axis display but it causes error because chart scale value is calculates and display on chart through a macro.

Temperarly I found solution of this problem. Here is how I did it. In my case/problem, to show values on y-axis scale is not important as long as chart updates with correct new data and corresponds to upper chart on date basis. On lower chart, double click on scale of y-axis, Format Axis window pops up/Numbers/Custom/Type, select "Yes";"Yes";"No" or "True";"True";"False" or "On";"On";"Off" or 0.E+00 or ##0E+0, click OK and problem is solved. I selected On/Off.

I will stick with it untill someone posts different solution. Thought to share with all of you making difference in learning of excel world. Thanks again for your consideration of this issue.

Riaz