ivymike:

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

Sub ChangeAxis()
    With Charts("Chart1").Axes(xlValue)
        .MinimumScale = Worksheets("Sheet1").Range("A1")
        .MaximumScale = Worksheets("Sheet1").Range("A2")
    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

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

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

    With Charts("Chart1").Axes(xlSecondary)
        .MinimumScale = Worksheets("Sheet1").Range("A3")
        .MaximumScale = Worksheets("Sheet1").Range("A4")
    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

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").ChartObjects("Chart 1").Chart.Axes(xlCategory)
        .MinimumScale = Worksheets("Sheet1").Range("A1")
        .MaximumScale = Worksheets("Sheet1").Range("A2")
    End With

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

Good Luck!

jproj

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.

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