Adding .xla button for Toggle Calculation Button
 

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

Adding .xla button for Toggle Calculation Button
 

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" wrote in message
...
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

Adding .xla button for Toggle Calculation Button
 

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" wrote:

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" wrote in message
...
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

Adding .xla button for Toggle Calculation Button
 

Did you follow the instructions on where to install it?

--

HTH

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


"Mike" wrote in message
...
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" wrote:

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" wrote in message
...
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

Adding .xla button for Toggle Calculation Button
 

I think I got it! I need to separate the procedures, and it worked! 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



"Bob Phillips" wrote:

Did you follow the instructions on where to install it?

--

HTH

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


"Mike" wrote in message
...
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" wrote:

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" wrote in message
...
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

Adding .xla button for Toggle Calculation Button
 

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

Bob


"Mike" wrote in message
...
I think I got it! I need to separate the procedures, and it worked!
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



"Bob Phillips" wrote:

Did you follow the instructions on where to install it?

--

HTH

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


"Mike" wrote in message
...
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" wrote:

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" wrote in message
...
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