Worksheet_Calculate event and input data

[Horatiu]

I'm new in VBA and my question might sound silly.
I've got a worksheet with 2 charts. The user inputs data .... some
computations ... and the data is plotted in 2 chartobjects. Both charts
have to have the same Y maximum scale. After the data is introduced,
the user should change a dataSeries in one chart (by mouse click and
drag) and the Y axis might change. Therefore I've triggered a Calculate
event to identify the change, recompute the new values in "chart 7" and
set the axis in "chart 7" equal to Y axis in "chart 2". (See code
below).

Private Sub Worksheet_Calculate()
ActiveSheet.ChartObjects("Chart 7").Activate
ActiveChart.Axes(xlValue).Select

With ActiveChart.Axes(xlValue)
.MaximumScale = ChartObjects("Chart
2").Chart.Axes(xlValue).MaximumScale
End With

End Sub

Everything works OK if the input data has already been introduced. If
the user decide to change the input data, the cursor in the worksheet
jumps all the time to the Y axis in "chart 7" as it is the active
object. How can I activate the next cell in the worksheet object,
without knowing if the user pushed RETURN or ARROW LEFT, ARROW RIGHT,
etc. There should be a very simple work around solution but I could not
find it.

Thx,
-horatiu-

 

[Jim Cone]

How about adding a "refresh" button to the sheet and eliminating
the calculate event?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Horatiu" <[email protected]>
wrote in message
I'm new in VBA and my question might sound silly.
I've got a worksheet with 2 charts. The user inputs data .... some
computations ... and the data is plotted in 2 chartobjects. Both charts
have to have the same Y maximum scale. After the data is introduced,
the user should change a dataSeries in one chart (by mouse click and
drag) and the Y axis might change. Therefore I've triggered a Calculate
event to identify the change, recompute the new values in "chart 7" and
set the axis in "chart 7" equal to Y axis in "chart 2". (See code
below).

Private Sub Worksheet_Calculate()
ActiveSheet.ChartObjects("Chart 7").Activate
ActiveChart.Axes(xlValue).Select

With ActiveChart.Axes(xlValue)
.MaximumScale = ChartObjects("Chart
2").Chart.Axes(xlValue).MaximumScale
End With

End Sub

Everything works OK if the input data has already been introduced. If
the user decide to change the input data, the cursor in the worksheet
jumps all the time to the Y axis in "chart 7" as it is the active
object. How can I activate the next cell in the worksheet object,
without knowing if the user pushed RETURN or ARROW LEFT, ARROW RIGHT,
etc. There should be a very simple work around solution but I could not
find it.

Thx,
-horatiu-

 

[Horatiu]

Jim, this is not what I had in mind. I need to go back to the default
worksheet behaviour.
-horatiu-

 

[Jim Cone]

-horatiu-
This eliminates the chart selection.
The active cell location should remain unchanged...

Private Sub Worksheet_Calculate()
Me.ChartObjects("Chart 7").Chart.Axes(xlValue).MaximumScale = _
Me.ChartObjects("Chart2").Chart.Axes(xlValue).MaximumScale
End Sub
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html


"Horatiu" <[email protected]>
wrote in message
Jim, this is not what I had in mind. I need to go back to the default
worksheet behaviour.
-horatiu-