Automatic updating excel chart
You can let Excel scale the axes automatically; when the charted values change, Excel updates the scales the way it thinks they fit best. We can use the has appeared automatically atop the module.
Or you can manually adjust the axis scales; when the charted values change, you must manually readjust the scales. You can write the entire procedure yourself, but it’s easier and more reliable to let the VB Editor start it for you.
While in the Options dialog, uncheck “Auto Syntax Check”.
This will save innumerable warnings about errors you already know about because the editor turns the font of the offending code red.
See how to set up axis-scaling formulas in Calculate Nice Axis Scales in Your Excel Worksheet.
The parts of Excel’s charting object model needed here are the property could also be controlled, but I usually do not show minor tick marks).
This example can be expanded to include secondary axes, or to change other charts as well.
Excel shines at turning your data into charts—graphical representations of your data.While automatic updating keeps a chart current, there may be times when you want to keep a snapshot of the data before an update.You could try making a copy of the chart before an update, but Excel is programmed to automatically update any copy of the chart, along with the original.Wouldn’t it be great to be able to link the axis scale parameters to values or, even better, formulas in the worksheet? There are a few pieces you need to make this technique work. Click on the left hand dropdown at the top of this module, and select Private Sub Worksheet_Change(By Val Target As Range) With Active Sheet. You need a chart, a set of values for the scaling parameters, and some VBA code to change the axis scales.
The code can be either linked to a button, or run from a event procedure.