event handler for calculation

[Spencer Hutton]

How do i intercept a change in Application.Calculation
I have a checkbox on a worksheet that says "Automatic Calculation" i want
it to be updated each time the calculation changes from automatic to manual.
if manual then unchecked, if automatic, then checked.

 

[JE McGimpsey]

There's no event that detects a change in option settings.

You could use other events to set the checkbox whenever they fire, e.g.:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
UpdateCheckbox
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
UpdateCheckbox
End Sub

Private Sub UpdateCheckbox()
Sheets("Sheet1").CheckBoxes("Check Box 1").Value = _
Application.Calculation = xlCalculationAutomatic
End Sub


But that won't guarantee the value's updated at any particular time.

You could use an OnTime macro to update every few seconds:

In the ThisWorkbook code module:

Private Sub Workbook_Open()
UpdateCheckbox bStart:=True
End Sub

In a regular code module:

Public Sub UpdateCheckbox(Optional bStart As Boolean = False)
Const cnSeconds As Long = 5
Static cb As CheckBox
Static nLast As Long
Dim nCalc As Long

If bStart Then
Set cb = Sheets("Sheet1").CheckBoxes("Check Box 1")
nLast = 0
End If
nCalc = Application.Calculation
If nCalc <> nLast Then
cb.Value = (nCalc = xlCalculationAutomatic)
nLast = nCalc
End If
Application.OnTime _
EarliestTime:=Now + TimeSerial(0, 0, cnSeconds), _
Procedure:="UpdateCheckbox", _
Schedule:=True
End Sub

 

[Spencer Hutton]

thanks, thats what i had done, i just wasnt sure if ther ewas another way.
thank you.

 

[JE McGimpsey]

Then thanks a *whole* lot for wasting my time....

 

[Spencer Hutton]

you're very welcome, have a nice day

Then thanks a *whole* lot for wasting my time....