event handler for calculation
 

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.

event handler for calculation
 

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), _
Procedu="UpdateCheckbox", _
Schedule:=True
End Sub


In article ,
Spencer Hutton wrote:

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.

event handler for calculation
 

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

"JE McGimpsey" wrote:

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), _
Procedu="UpdateCheckbox", _
Schedule:=True
End Sub


In article ,
Spencer Hutton wrote:

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.

event handler for calculation
 

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


In article ,
Spencer Hutton wrote:

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

event handler for calculation
 

you're very welcome, have a nice day

"JE McGimpsey" wrote:

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


In article ,
Spencer Hutton wrote:

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