X-Y scatter plot axis range

[ivymike]

Anybody know a good way to set the axis range limits of a scatter plot equal to values in a few cells?

 

[jproj]

ivymike:

I could only get it to work on the y-axis. Here is the code I used though...

Sub ChangeAxis()
With Charts("Chart1&quot.Axes(xlValue)
.MinimumScale = Worksheets("Sheet1&quot.Range("A1&quot
.MaximumScale = Worksheets("Sheet1&quot.Range("A2&quot
End With
End Sub

here the min value for the y-axis is in cell "A1", max value in "A2". I'm still trying to figure out the x-axis... If I figure it out, I'll let you know.

jproj

 

[jproj]

Ok, disregard that last one... I played around and figured it out:

Sub ChangeAxis()
With Charts("Chart1&quot.Axes(xlPrimary)
.MinimumScale = Worksheets("Sheet1&quot.Range("A1&quot
.MaximumScale = Worksheets("Sheet1&quot.Range("A2&quot
End With

With Charts("Chart1&quot.Axes(xlSecondary)
.MinimumScale = Worksheets("Sheet1&quot.Range("A3&quot
.MaximumScale = Worksheets("Sheet1&quot.Range("A4&quot
End With
End Sub

xlPrimary changes the x-axis and xlSecondary changes the y-axis. (so Xmin = A1, Xmax = A2, Ymin = A3, and Ymax = A4)
I wasn't able to run the macro unless the chart was in it's own sheet, but I'm sure that's just a matter of referencing it correctly. (If you haven't already noticed, I haven't ever done this before).

Anyway, I hope this helps!

Jproj

 

[jproj]

Ok, (last one)... here's another way I got it to work using a chart on "sheet1" with a command buttom on the same sheet:

Private Sub CommandButton1_Click()
With Worksheets("Sheet1&quot.ChartObjects("Chart 1&quot.Chart.Axes(xlCategory)
.MinimumScale = Worksheets("Sheet1&quot.Range("A1&quot
.MaximumScale = Worksheets("Sheet1&quot.Range("A2&quot
End With

With Worksheets("Sheet1&quot.ChartObjects("Chart 1&quot.Chart.Axes(xlValue)
.MinimumScale = Worksheets("Sheet1&quot.Range("A3&quot
.MaximumScale = Worksheets("Sheet1&quot.Range("A4&quot
End With
End Sub

Good Luck!

jproj

 

[ivymike]

Oops, sorry, I wasn't specific enough in my original question. I know how to do a one-time adjustment by the method you mentioned, I was wondering if there was a good way to tie the max and min of the axis to a particular pair of cells, such that updates to those cells are automatically reflected in the chart. I'd like to set the chart up to automatically scale to accomodate new data, but the built-in auto scaling isn't getting the picture right.

 

[jproj]

I'm doubt you want to do this, but you could assign a macro to your chart. The only other way I can think to do this is to have an "Update Now" command button that updates each axis for the max and min values of your data range. Either of these ways, you'll have to click on something to update your chart. I don't know how (or if it's even possible) to update your chart upon changing any cell.

Sorry for all the worthless postings

jproj