Toggle Switch

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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.
 
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
 
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top