I currently have the below macros assigned to 2 different buttons. I would like the user to have one 1 button that turns green and says “Auto Refresh ON” when on and then turns red and says “Auto Ref

I currently have the below macros assigned to 2 different buttons.  I would like the user to have one 1 button that turns green and says “Auto Refresh ON” when on and then turns red and says “Auto Refresh OFF” when it’s off.

Sub StartTimer()
    TimerSeconds = 5 ' how often to "pop" the timer in seconds.
    TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf ClickRefresh)
End Sub

Open in new window

Sub EndTimer()
    On Error Resume Next
    KillTimer 0&, TimerID
End Sub

Open in new window

kbay808Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
So do you want a 3rd button or do you want just one button that toggles between green and red?
0
Martin LissOlder than dirtCommented:
If you just want one button then try this

Private Sub Workbook_Open()
    Sheets("Sheet1").CommandButton1.Caption = "Auto Refresh ON"
    Sheets("Sheet1").CommandButton1.BackColor = vbGreen

End Sub

Open in new window

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
If CommandButton1.Caption = "Auto Refresh ON" Then
    CommandButton1.Caption = "Auto Refresh OFF"
    CommandButton1.BackColor = vbRed
    TimerSeconds = 5 ' how often to "pop" the timer in seconds.
    TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf ClickRefresh)
Else
    CommandButton1.Caption = "Auto Refresh ON"
    CommandButton1.BackColor = vbGreen
    On Error Resume Next
    KillTimer 0&, TimerID
End If
Application.ScreenUpdating = True
End Sub

Open in new window

0
kbay808Author Commented:
The functions were backwards, but your code works great.  Is there a way that if there is an error that the caption could be changed to "Auto Refresh OFF" so that it will end the macro?

Private Sub CommandButton21_Click()
Application.ScreenUpdating = False
If CommandButton21.Caption = "Auto Refresh ON" Then
    CommandButton21.Caption = "Auto Refresh OFF"
    CommandButton21.BackColor = vbRed
    On Error Resume Next
    KillTimer 0&, TimerID
Else
    CommandButton21.Caption = "Auto Refresh ON"
    CommandButton21.BackColor = vbGreen
    TimerSeconds = 5 ' how often to "pop" the timer in seconds.
    TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf ClickRefresh)
    
End If
Application.ScreenUpdating = True

End Sub

Open in new window

0
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Martin LissOlder than dirtCommented:
In some other routine

On Error GoTo ErrorRoutine
' existing code

' new code
Exit Sub
ErrorRoutine:

' This will call the other button's Click event and toggle it
CommandButton1_Click

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kbay808Author Commented:
Thank you very much for all of your help.  Have a great weekend.
0
Martin LissOlder than dirtCommented:
I'm retired so it's all weekend for me:) In any case you're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.