Toggle Switch
 

Bob gave me the following macro to switch from calculation auto to
calculation manual and works just fine. What I would like to next is to
change the color of the button from Red if manual to Green if automatic. Can
do?


Sub ToggleCalculation()
With Application
If .Calculation = xlCalculationManual Then
.Calculation = xlCalculationAutomatic
Else
.Calculation = xlCalculationManual
End If
End With
End Sub

Toggle Switch
 

You'll need to know the name of your command button. The code below is
using "CommandButton3".

Sub ToggleCalculation()

If Application.Calculation = xlCalculationManual _
Then
With ActiveSheet.OLEObjects("CommandButton3").Object
.BackColor = &HFF00&
.Caption = "Auto"
.ForeColor = &H0&
End With
Application.Calculation = xlCalculationAutomatic
Else
With ActiveSheet.OLEObjects("CommandButton3").Object
.BackColor = &HFF&
.Caption = "Manual"
.ForeColor = &HFFFFFF
End With
Application.Calculation = xlCalculationManual
End If
End Sub


HTH,
Paul

"Beep Beep" wrote in message
...
Bob gave me the following macro to switch from calculation auto to
calculation manual and works just fine. What I would like to next is to
change the color of the button from Red if manual to Green if automatic.
Can
do?


Sub ToggleCalculation()
With Application
If .Calculation = xlCalculationManual Then
.Calculation = xlCalculationAutomatic
Else
.Calculation = xlCalculationManual
End If
End With
End Sub

Toggle Switch
 

What kind of button - control toolbox, or forms ?

Tim


"Beep Beep" wrote in message
...
Bob gave me the following macro to switch from calculation auto to
calculation manual and works just fine. What I would like to next is to
change the color of the button from Red if manual to Green if automatic.
Can
do?


Sub ToggleCalculation()
With Application
If .Calculation = xlCalculationManual Then
.Calculation = xlCalculationAutomatic
Else
.Calculation = xlCalculationManual
End If
End With
End Sub

Toggle Switch
 

It is impossible to see from your code what kind of cutton you are talking
about.

If from the forms toolbar, then no.

Why not use a toggle button from the control toolbox toolbar and then you
shouldn't have to change the color.

--
Regards,
Tom Ogilvy



"Beep Beep" wrote:

Bob gave me the following macro to switch from calculation auto to
calculation manual and works just fine. What I would like to next is to
change the color of the button from Red if manual to Green if automatic. Can
do?


Sub ToggleCalculation()
With Application
If .Calculation = xlCalculationManual Then
.Calculation = xlCalculationAutomatic
Else
.Calculation = xlCalculationManual
End If
End With
End Sub

Toggle Switch
 

Custom Button with a smiley face on the toolbar

"Tim" wrote:

What kind of button - control toolbox, or forms ?

Tim


"Beep Beep" wrote in message
...
Bob gave me the following macro to switch from calculation auto to
calculation manual and works just fine. What I would like to next is to
change the color of the button from Red if manual to Green if automatic.
Can
do?


Sub ToggleCalculation()
With Application
If .Calculation = xlCalculationManual Then
.Calculation = xlCalculationAutomatic
Else
.Calculation = xlCalculationManual
End If
End With
End Sub

Toggle Switch
 

Sub ToggleCalculation()
With Application
If .Calculation = xlCalculationManual Then
.Calculation = xlCalculationAutomatic
Else
.Calculation = xlCalculationManual
End If
End With
End Sub

All of the above subroutine's code can be replaced with this single line...

Sub ToggleCalculation()
Application.Calculation = (xlCalculationManual + xlCalculationAutomatic) - Application.Calculation
End Sub

Rick

Toggle Switch
 

Thanks Rich, however I am trying to determine if I can have the button on the
toolbar (custom button) to change colors when the calculation changes from
manual to automatic or vice versus.

"Rick Rothstein (MVP - VB)" wrote:

Sub ToggleCalculation()
With Application
If .Calculation = xlCalculationManual Then
.Calculation = xlCalculationAutomatic
Else
.Calculation = xlCalculationManual
End If
End With
End Sub

All of the above subroutine's code can be replaced with this single line...

Sub ToggleCalculation()
Application.Calculation = (xlCalculationManual + xlCalculationAutomatic) - Application.Calculation
End Sub

Rick

Toggle Switch
 

No you can't. You might want to change the icon on the button.
--
Regards,
Tom Ogilvy


"Beep Beep" wrote:

Thanks Rich, however I am trying to determine if I can have the button on the
toolbar (custom button) to change colors when the calculation changes from
manual to automatic or vice versus.

"Rick Rothstein (MVP - VB)" wrote:

Sub ToggleCalculation()
With Application
If .Calculation = xlCalculationManual Then
.Calculation = xlCalculationAutomatic
Else
.Calculation = xlCalculationManual
End If
End With
End Sub

All of the above subroutine's code can be replaced with this single line...

Sub ToggleCalculation()
Application.Calculation = (xlCalculationManual + xlCalculationAutomatic) - Application.Calculation
End Sub

Rick