Toggle Switch

[Guest]

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

 

[PCLIVE]

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

 

[Tim]

What kind of button - control toolbox, or forms ?

Tim

 

[Guest]

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.

 

[Guest]

Custom Button with a smiley face on the toolbar

What kind of button - control toolbox, or forms ?

Tim

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Guest]

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.

 

[Guest]

No you can't. You might want to change the icon on the button.