Formatting Y axis values based on other chart

[AmyTaylor]

Can anyone help me with this problem, or let me know if it is no
possible in Excel ?

I have 2 charts, but want to use the Y-axis values from the 1st char
for the 2nd chart too. The values on the 1st chart can be automatic
but I want the 2nd chart to be plotted on the same scale as the firs
without having to go in an manually change the minimum and maximu
values.

Can this be done in VBA ?
Thanks for any help you can give.
Amy x

 

[Guest]

To work with a chart in VBA, first you need to know how to reference it. If
it is a chart sheet, it would be ThisWorkbook.Charts("SheetName"). If the
charts are objects placed on a "regular" sheet then you use
Worksheets("SheetName").ChartObjects(n).Chart (where n is an index number for
the chart).

Then, to get at the Y axis scale, you would use the Axes(xlValue) property:
from here there are several properties related to scale (corresponding to the
properties you see when setting up the chart manually, the most relevant ones
being:
MinimumScale
MaximumScale
MajorUnit
MinorUnit

So you could copy from your first chart to the second:

Dim Chart1YAxis as Axis, Chart2YAxis as Axis
Set Chart1YAxis = Worksheets("SheetName").ChartObjects(1).Chart.Axes(xlValue)
Set Chart2YAxis = Worksheets("SheetName").ChartObjects(2).Chart.Axes(xlValue)
With Chart2YAxis
.MinimumScale = Chart1YAxis.MinimumScale
.MaximumScale = Chart1YAxis.MaximumScale
.MajorUnit = Chart1YAxis.MajorUnit
.MinorUnit = Chart1YAxis.MinorUnit
End With

HTH!

 

[AmyTaylor]

Dales, thanks for your reply, and your help
I will give your suggestion a shot, and get back to you if it doesnt
help....if I may
Thank you again
Amy xx