How do I make a Toggle Button work?

[andy-s]

Hi,

I've two macros that I want to link to a toggle button.

My idea is that the button should work like a power switch knop; macro nr 1 should start if I push the button (true=on), and macro nr 2 should begin as soon as I hit the button for the second time (false=off).

And, is it possible to display a button text like "On" and "Off"?

..thx..

 

[smozgur]

Hi Andy,

Set your button's action macro as RunMe and use this code. State property which you are looking for to toggle action. And Caption property as the appearing text on button. My command button is on Formatting toolbar as id is 22.

Sub RunMe()
With Application.CommandBars("Formatting").Controls(22)
Select Case .Caption
Case "&On"
.Caption = "&Off"
.State = msoButtonDown
'Call Your On Macro, first macro may be
Case "&Off"
.Caption = "&On"
.State = msoButtonUp
'Call Your Off Macro, other macro
End Select
End With
End Sub

regards