Ms Excel found a problem and has to be finishedClosed

Question

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

rizvisa1 · Answer

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

juppkk · Answer

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?

juppkk · Answer

> "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)

juppkk · Answer

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

Ms Excel found a problem and has to be finished

4 replies

Subscribe To Our Newsletter!