Adding .xla button for Toggle Calculation Button

[Mike]

I have a macro to toggle the calcuation button, but wish to convert it to an
..xla that I can share with my co-workers more easily. Does anyone know how to
do this so that the macro exists along with the button? I am not experienced
in this area.

Also, does anyone know how to improve the macro so that I can tell by the
button image if calc is on or off? Thanks much! Here is the macro below.
Sub ToggleApplicationCalculation()
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
End Sub 'ToggleApplicationCalculation

 

[Bob Phillips]

Build the button on add-in open

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

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Mike]

I am having trouble to get this to work properly. It seems that I can add a
button by selection the add-in and some other items, but it seems like it
does not work consistently. Also, when I hardcode a breakpoint "stop" on the
workbookopen, it does not stop there. Any ideas? Thanks much!

 

[Bob Phillips]

Did you follow the instructions on where to install it?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Mike]

I think I got it! I need to separate the procedures, and it worked! Thanks!

 

[Bob Phillips]

Sorry, I made the assumption your code was in a standard code module. I'll
know better in future

Bob