Change Event and Calculated cells

T

tbieri

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
 
J

JB

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
 
G

Guest

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
Click to expand...
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,
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
Source:=Range(Sheet1.Cells(iFirstRow, 1),
Sheet1.Cells(iLastRow, 2)), PlotBy:=xlColumns
 
J

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).MinimumScale = Range("$H$42").Valu
ActiveWorkbook.Charts(1).Chart.Axes(xlCategory).MaximumScale = 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).MinimumScale = Range("MINSCALE").Valu
ActiveWorkbook.Charts(1).Chart.Axes(xlCategory).MaximumScale = 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).MinimumScale = m_sgMi
End I

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

End Su

Regards

JB


----- [email protected] 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
Click to expand...
worksheet calculates, and will trigger your code when yo
want it tobut I don't think you can do it for the x-axis (I might b
wrong)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 jobfirst and last rows to plot are calculated in cells E1 an
F1, your code could look something like this
iLastRow = Sheet1.Range("F1").Valu
ActiveChart.SetSourceDat
Click to expand...
Source:=Range(Sheet1.Cells(iFirstRow, 1)
Sheet1.Cells(iLastRow, 2)), PlotBy:=xlColumn
 
G

Guest

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
Click to expand...
x-axis min/max - it'll come in handy.
The 'Worksheet_Change' event tells you the Range that has
Click to expand...
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
Click to expand...
whether the cells of interest have changed. I think your
solution could be simpler - this should work:
Option Explicit

Private Sub Worksheet_Calculate()
Click to expand...
ActiveWorkbook.Charts(1).Chart.Axes(xlCategory).MinimumScale
= Range("$H$42").ValueActiveWorkbook.Charts(1).Chart.Axes(xlCategory).MaximumScale
= Range("$H$43").Value
End Sub

If you ever insert/delete rows or columns in the
Click to expand...
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()
Click to expand...
ActiveWorkbook.Charts(1).Chart.Axes(xlCategory).MinimumScale
= Range("MINSCALE").ValueActiveWorkbook.Charts(1).Chart.Axes(xlCategory).MaximumScale
= Range("MAXSCALE").Value
End Sub

As a final thought, this code will execute every time the
Click to expand...
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
Click to expand...
ActiveWorkbook.Charts(1).Chart.Axes(xlCategory).MinimumScale
= m_sgMin
End If

If Range("MAXSCALE").Value <> m_sgMax Then
m_sgMax = Range("MAXSCALE").Value
Click to expand...
ActiveWorkbook.Charts(1).Chart.Axes(xlCategory).MaximumScale
= m_sgMax
 
Top