JB,

Thanks for the help, and I tried to implement the third
example. The "Activeworkbook" kept returning an error
"Object does not support this property or method."

I did some poking around and came up with the following:
With Charts("Name").Axes(xlCategory)
.MinimumScale = m_sgMin
End With

This appears to work and does the trick. Thanks again.

Regards,
Tim
-----Original Message-----
Tim.

Nice. I'm glad I was wrong about being able to change the
x-axis min/max - it'll come in handy.

The 'Worksheet_Change' event tells you the Range that has
been changed by passing you the 'Target' Range Object:

Private Sub Worksheet_Change(ByVal Target As Range)

The 'Worksheet_Calculate' event doesn't:

Private Sub Worksheet_Calculate()

A lot of the code you've pasted below is to identify
whether the cells of interest have changed. I think your
solution could be simpler - this should work:

Option Explicit

Private Sub Worksheet_Calculate()


ActiveWorkbook.Charts(1).Chart.Axes(xlCategory).Mi nimumScale
= Range("$H$42").Value

ActiveWorkbook.Charts(1).Chart.Axes(xlCategory).Ma ximumScale
= Range("$H$43").Value

End Sub

If you ever insert/delete rows or columns in the
worksheet, the correct cell references may no longer be
$H$42 and $H$43. It's good practice to give names to the
two cells and use them in your code to make it more robust,
e.g.:

Option Explicit

Private Sub Worksheet_Calculate()


ActiveWorkbook.Charts(1).Chart.Axes(xlCategory).Mi nimumScale
= Range("MINSCALE").Value

ActiveWorkbook.Charts(1).Chart.Axes(xlCategory).Ma ximumScale
= Range("MAXSCALE").Value

End Sub

As a final thought, this code will execute every time the
worksheet recalculates, so you could remember the minimum
and maximum values and only change the chart if necessary,
e.g.:

Option Explicit

Private m_sgMin as Single
Private m_sgMax as Single

Private Sub Worksheet_Calculate()

If Range("MINSCALE").Value < m_sgMin Then
m_sgMin = Range("MINSCALE").Value

ActiveWorkbook.Charts(1).Chart.Axes(xlCategory).Mi nimumScale
= m_sgMin
End If

If Range("MAXSCALE").Value < m_sgMax Then
m_sgMax = Range("MAXSCALE").Value

ActiveWorkbook.Charts(1).Chart.Axes(xlCategory).Ma ximumScale
= m_sgMax
End If

End Sub

Regards,

JB.


----- wrote: -----

JB,

Thanks for your post, here is what I have so far...

Option Explicit
Private Sub Worksheet_Calculate()

Dim target As Range

Select Case target.Address
Case "$H$42"
ActiveWorkbook.Charts(1).Chart.Axes(xlCategory) _
.MinimumScale = target.Value
Case "$H$43"
ActiveWorkbook.Charts(1).Chart.Axes(xlCategory) _
.MaximumScale = target.Value
Case Else
End Select
End Sub

The guts for this code come from Jon Peltier's web site,
but his example uses the Change event versus the
Calculate
event. Right now it bombs on the Select Case line,
probably due to the Dim statement, but I am at a loss.

Regards,
Tim
-----Original Message-----
Hi Tim.
The 'Worksheet_Calculate' Event is fired after the
worksheet calculates, and will trigger your code when you
want it to.
It's easy to change the min and max values of the
y-axis,
but I don't think you can do it for the x-axis (I
might be
wrong).
If you need an alternative, you could use two other
cells
to calculate the first row and last row of the data you
want to plot (assuming the values are in columns). The
MATCH function would probably do the job.
If the data is in Sheet1 columns A:B, for example,
and the
first and last rows to plot are calculated in cells
E1 and
F1, your code could look something like this:
Private Sub Worksheet_Calculate()
Dim iFirstRow As Integer
Dim iLastRow As Integer
iFirstRow = Sheet1.Range("E1").Value
iLastRow = Sheet1.Range("F1").Value
Sheet1.ChartObjects("Chart 1").Activate
ActiveChart.SetSourceData
Source:=Range(Sheet1.Cells(iFirstRow, 1),
Sheet1.Cells(iLastRow, 2)), PlotBy:=xlColumns
End Sub
Hope this helps.
JB.
.

.