Howdy;

I am interested in using the worksheet code to look at two
cells, which in turn become the X-axis scale max/min. The
cells are calculated, and based on Cpearson.com, Change
Event does not fire from calculated cells. Ultimatley the
cell changes based on the user selection from a Form
dropdown.

For example, the dropdown contains 7 day, 30 day, 90 day
options and the changing cells find the right begin/end dates.

I have the whole process working based on dyanmic ranges,
but it seems cleaner/easier to graph the whole dataset and
adjust the scale limits.

Regards,
Tim

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 Intege
Dim iLastRow As Intege

iFirstRow = Sheet1.Range("E1").Valu
iLastRow = Sheet1.Range("F1").Valu

Sheet1.ChartObjects("Chart 1").Activat
ActiveChart.SetSourceData Source:=Range(Sheet1.Cells(iFirstRow, 1), Sheet1.Cells(iLastRow, 2)), PlotBy:=xlColumn

End Su

Hope this helps

JB

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.


.

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 Explici

Private Sub Worksheet_Calculate(

ActiveWorkbook.Charts(1).Chart.Axes(xlCategory).Mi nimumScale = Range("$H$42").Valu
ActiveWorkbook.Charts(1).Chart.Axes(xlCategory).Ma ximumScale = Range("$H$43").Valu

End Su

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 Explici

Private Sub Worksheet_Calculate(

ActiveWorkbook.Charts(1).Chart.Axes(xlCategory).Mi nimumScale = Range("MINSCALE").Valu
ActiveWorkbook.Charts(1).Chart.Axes(xlCategory).Ma ximumScale = Range("MAXSCALE").Valu

End Su

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 Explici

Private m_sgMin as Singl
Private m_sgMax as Singl

Private Sub Worksheet_Calculate(

If Range("MINSCALE").Value < m_sgMin The
m_sgMin = Range("MINSCALE").Valu
ActiveWorkbook.Charts(1).Chart.Axes(xlCategory).Mi nimumScale = m_sgMi
End I

If Range("MAXSCALE").Value < m_sgMax The
m_sgMax = Range("MAXSCALE").Valu
ActiveWorkbook.Charts(1).Chart.Axes(xlCategory).Ma ximumScale = m_sgMa
End I

End Su

Regards

JB


----- wrote: ----

JB

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

Option Explici
Private Sub Worksheet_Calculate(

Dim target As Rang

Select Case target.Addres
Case "$H$42
ActiveWorkbook.Charts(1).Chart.Axes(xlCategory)
.MinimumScale = target.Valu
Case "$H$43
ActiveWorkbook.Charts(1).Chart.Axes(xlCategory)
.MaximumScale = target.Valu
Case Els
End Selec
End Su

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

Regards
Ti
-----Original Message----
Hi Tim
The 'Worksheet_Calculate' Event is fired after th
worksheet calculates, and will trigger your code when yo
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 b
wrong)
If you need an alternative, you could use two other cell
to calculate the first row and last row of the data yo
want to plot (assuming the values are in columns). Th
MATCH function would probably do the job
If the data is in Sheet1 columns A:B, for example, and th
first and last rows to plot are calculated in cells E1 an
F1, your code could look something like this
Private Sub Worksheet_Calculate(
Dim iFirstRow As Intege
Dim iLastRow As Intege
iFirstRow = Sheet1.Range("E1").Valu
iLastRow = Sheet1.Range("F1").Valu
Sheet1.ChartObjects("Chart 1").Activat
ActiveChart.SetSourceDat
Source:=Range(Sheet1.Cells(iFirstRow, 1)
Sheet1.Cells(iLastRow, 2)), PlotBy:=xlColumn
End Su
Hope this helps
JB

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.
.

.