Toggle Calc Button - how to make it work OK at startup
Hi Mike,
Sorry, I missed this post earlier!
I would like to add one more part where it does
changes the button to pressed/up at STARTUP, but I cannot get it to work.
Any
ideas? This is greatly appreciated!
In the Workbook_Open code, chamge
.State = msoButtonUp
to
.State = msoButtonDown
Incidentally, the code you show with this post has been superceded earlier
in the thread.
To avoid confusion the code should read:
'==============================
'\\ In the ThisWorkbook module
'----------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"CalculateToggle").Delete
On Error GoTo 0
End Sub
Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"CalculateToggle").Delete
On Error GoTo 0
With Application.CommandBars("Standard")
With .Controls.Add(Temporary:=True)
.BeginGroup = True
.Style = msoButtonIcon
.FaceId = 283
.Caption = "CalculateToggle"
.State = msoButtonDown
.OnAction = "ToggleApplicationCalculation"
End With
End With
End Sub
'<<==============================
And:
'==============================
'\\ In a standard module
------------------------
Sub ToggleApplicationCalculation()
With Application.CommandBars.ActionControl
If .State = msoButtonUp Then
.State = msoButtonDown
Else
.State = msoButtonUp
End If
End With
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
'<<==============================
---
Regards,
Norman
"Mike" wrote in message
...
Thanks for all the other help! I am trying to make a button that will be
depress/up if the Calculation is Off/On. I am able to make it work, but
only
AFTER the button is pushed. I would like to add one more part where it
does
changes the button to pressed/up at STARTUP, but I cannot get it to work.
Any
ideas? This is greatly appreciated!
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
Sub ToggleApplicationCalculation()
On Error GoTo ErrorHandler
If Application.Calculation = xlManual Then
Application.Calculation = xlAutomatic
Application.CommandBars.ActionControl.State = msoButtonUp
MsgBox "Calculation toggled to Automatic."
Else
Application.Calculation = xlManual
Application.CalculateBeforeSave = True
Application.CommandBars.ActionControl.State = msoButtonDown
MsgBox "Calculation toggled to Manual."
End If
ErrorHandler:
End Sub 'ToggleApplicationCalculation
|