PDA

View Full Version : calculation state



lior03
04-05-2007, 06:23 AM
hello
i am trying to let excel tell me what calculation mode is currentlly active.
please correct the following:

Sub caculatem()
Dim x As String
Dim ireply As VbMsgBoxResult
If Application.Calculation = xlCalculationAutomatic Then Exit Sub
MsgBox "calculation currentlly is manual,change to automatic?", vbYesNo + vbCritical, "calculation state"
If ireply = vbYes Then
Application.Calculation = xlCalculationAutomatic
End If
End Sub


thanks

moa
04-05-2007, 06:39 AM
Sub caculatem()

If Not Application.Calculation = xlCalculationAutomatic Then

If MsgBox("calculation currentlly is manual,change to automatic?", _
vbYesNo + vbCritical, "calculation state") = vbYes Then
Application.Calculation = xlCalculationAutomatic
End If

End If

End Sub

mvidas
04-05-2007, 06:44 AM
Hi moshe,

If you don't care about the semiautomatic/manual difference, Glen's code would be perfect. If the difference matters, you could do something like Sub caculatem()
Dim vCalc As String
Select Case Application.Calculation
Case xlCalculationAutomatic: Exit Sub
Case xlCalculationManual: vCalc = "manual"
Case xlCalculationSemiautomatic: vCalc = "automatic except for tables"
End Select
If MsgBox("Calculation currently is set to " & vCalc & ", change to fully " & _
"automatic?", vbYesNo + vbCritical, "Calculation state") = vbYes Then _
Application.Calculation = xlCalculationAutomatic
End SubMatt

Bob Phillips
04-05-2007, 11:31 AM
I have a toolbar button that I use to toggle the calculaton mode and I set the tooltiptext to show me what it is currently



Option Explicit
Option Private Module

Declare Function GetKeyState Lib "user32" (ByVal fnKey As Long) As Integer

Const vkShift As Integer = &H10

Private Sub SetCalculateMode() 'Shifted is automatic, unshifted is manual
Dim sMode As String
Dim nState As Long

If GetKeyState(vkShift) < 0 Then
Application.Calculation = xlAutomatic
sMode = "Automatic"
nState = msoButtonDown
Else
Application.Calculation = xlManual
sMode = "Manual"
nState = msoButtonUp
End If
With Application.CommandBars.ActionControl
.TooltipText = "Calculation mode is " & sMode
.State = nState
End With

End Sub