M
Mike
Thanks for all help. I was able to get the Toggle Calc .xla add-in working
with the following code. However, I can't figure out how to change this macro
to be "depressed" when Calc is ON, and "pushed up" when the Calc is OFF. Any
help? I cannot get msoButtonUp, msoButtonDown, and .State to work at all.
Thanks!
**** IN THE MODULE'S VBA ****
Sub ToggleApplicationCalculation()
On Error GoTo ErrorHandler
If Application.Calculation = xlManual Then
Application.Calculation = xlAutomatic
MsgBox "Calculation toggled to Automatic."
Else
Application.Calculation = xlManual
Application.CalculateBeforeSave = True
MsgBox "Calculation toggled to Manual."
End If
ErrorHandler:
End Sub 'ToggleApplicationCalculation
**** IN THISWORKBOOK'S VBA ****
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete
On Error GoTo 0
End Sub
Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete
On Error GoTo 0
With Application.CommandBars("Standard")
With .Controls.Add(Temporary:=True)
.BeginGroup = True
.Style = msoButtonIcon
.FaceId = 283
.Caption = "CalculateToggle"
.OnAction = "ToggleApplicationCalculation"
End With
End With
End Sub
with the following code. However, I can't figure out how to change this macro
to be "depressed" when Calc is ON, and "pushed up" when the Calc is OFF. Any
help? I cannot get msoButtonUp, msoButtonDown, and .State to work at all.
Thanks!
**** IN THE MODULE'S VBA ****
Sub ToggleApplicationCalculation()
On Error GoTo ErrorHandler
If Application.Calculation = xlManual Then
Application.Calculation = xlAutomatic
MsgBox "Calculation toggled to Automatic."
Else
Application.Calculation = xlManual
Application.CalculateBeforeSave = True
MsgBox "Calculation toggled to Manual."
End If
ErrorHandler:
End Sub 'ToggleApplicationCalculation
**** IN THISWORKBOOK'S VBA ****
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete
On Error GoTo 0
End Sub
Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete
On Error GoTo 0
With Application.CommandBars("Standard")
With .Controls.Add(Temporary:=True)
.BeginGroup = True
.Style = msoButtonIcon
.FaceId = 283
.Caption = "CalculateToggle"
.OnAction = "ToggleApplicationCalculation"
End With
End With
End Sub