Ms Excel found a problem and has to be finished

Closed
Report
Posts
4
Registration date
Monday May 6, 2013
Status
Member
Last seen
May 8, 2013
-
Posts
4
Registration date
Monday May 6, 2013
Status
Member
Last seen
May 8, 2013
-
Hello,
XP, Excel 2002

I have a data sheet with a Line-gragh and scroll bars (up/down)
The scroll bar shut increase/decrease a cell value. Done by mako.

Private Sub SpinButton1_SpinUp()
Range("B73") = Application.WorksheetFunction.MIN(Range("B73") + 1, 63)
Call ruc1
End Sub

After changing the cell value sub ruc1 is called to rescales the y-axis of the graph.

Sub ruc1()
mx = CInt((Range("d77") - Range("c77")) / 3 * 35) / 10
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = Range("c77") - mx
.MaximumScale = Range("d77") + mx
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
' .Crosses = xlAutomatic
.Crosses = xlCustom
.CrossesAt = Range("c77") - mx
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub

It works as expected. But after first ie.: spinup and the job is done, Excel is -in the cloud- .
Then, after entering a cell with the mouse excel stops working with the popup message:
"Microsoft Excel has found a problem and has to be finished"

Each function as stand allone works fine.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Sheets("Tabelle2").Range("B73")) Is Nothing Then Exit Sub
'sub ruc1()
mx = . . .

In this case Srollbars will only increase/decrease cell value of B73
Changing cell value by cell input will start rescaling.
No problem. But I want to combine these functions.

Any help?

Regards juppkk

4 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
try with this


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Sheets("Tabelle2").Range("B73")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Application.Calculation = xlCalculationManual
call ruc1()
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

end sub
Posts
4
Registration date
Monday May 6, 2013
Status
Member
Last seen
May 8, 2013

Hi,
thank you for awnsering.
But I'm sorry, I don't understand what you mean.

Private Sub Worksheet_Change(ByVal Target As Range)
is the routine to change Y-Axis

ruc1 is also the routine to change Y-Axis
even called "Private Sub Worksheet_Change(ByVal Target As Range)"

What's about the scroll bars?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
"Private Sub Worksheet_Change(ByVal Target As Range) " is an event
That would be fired when there is a change in the worksheet
I presumed that you were trying to call your ruc1 function if there is a change in the sheet. I must be then missing your point here.
Posts
4
Registration date
Monday May 6, 2013
Status
Member
Last seen
May 8, 2013

> "Private Sub Worksheet_Change(ByVal Target As Range) " is an event
> That would be fired when there is a change in the worksheet
yes and that works fine if I change cells manually.

As soon as I call this as a subroutine (ruc1) from scroll bar event I get the error.
or in short:
----
Private Sub SpinButton1_SpinUp()
Range("B73") = Application.WorksheetFunction.MIN(Range("B73") + 1, 63)

ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.Axes(xlValue).Select
' rest of routine just set as comment or not
End Sub
----
will end in: popup Message:
"Microsoft Excel has found a problem and has to be finished"
(after the job is well done)
Posts
4
Registration date
Monday May 6, 2013
Status
Member
Last seen
May 8, 2013

Hi,

I got it.

The problem was as expected the combination of SpinButton and
the Y-Axis scaling routine.
After handling this both routines Excel was "flying in the cloud".
And the next mouseclick anywher at the map forced the break down.

However, just setting a defined position at then end of Y-Axis routine helped.
. . .
Range("b73").Select
End Sub

Regards juppkk