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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> >
>
>
>

Did you follow the instructions on where to install it?

--

HTH

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


"Mike" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > >
> > >
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>
>

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

Bob


"Mike" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >