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