Loop-de-loop (2000/2002)

[Leif]

I wish to control a macro to make it run (say) every 3 seconds when a button is clicked, and stop if another is clicked.
I can get the macro - which takes around 2 seconds to run - to loop for ever, but lose the control for the loop to see that the other button has been clicked and I want it to stop!
Any clues please?

[HansV]

Leif,

You can define a public variable at the top of a standard module: Dim fStop As Boolean. Set fStop to False in the On Click code of the first button, then call a function to be executed, and set it to True in the On Click code of the second button. In the function, test if fStop is still false, then execute whatever you wanted to do, and then call the function using OnTime. This probably sounds confusing, so here is skeleton code to illustrate it:

Public fStop As Boolean

Sub Button1Click()
fStop = False
MyAction
End Sub

Sub Button2Click()
fStop = True
End Sub

Function MyAction()
If fStop = False Then
' do something here
Beep
' then repeat
Application.OnTime Now + TimeSerial(0, 0, 3), "MyAction"
End If
End Function

[Leif]

Perfect for my needs ! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

I have even managed to get one button to toggle True/False - is there an easy way to toggle the caption from (e.g.) Start > Stop?

[HansV]

Is this a button on a UserForm or on a worksheet? And if it is on a worksheet, did you create it using the Forms toolbar or using the Control Toolbox?

[Leif]

Created using the Control Toolbox on a Worksheet....

[HansV]

OK, then it should be easy. The On Click code for the command button could look like this:

Private Sub CommandButton1_Click()
If fStop = True Then
fStop = False
CommandButton1.Caption = "Stop"
MyAction
Else
fStop = True
CommandButton1.Caption = "Run"
End If
End Sub

Replace CommandButton1 by the name of the command button.

[Andrew Cronnolly]

If Leif is using an actual ToggleButton then

Private Sub ToggleButton1_Click()
With Me.ToggleButton1
If .Value Then
.Caption = "Start"
Else
.Caption = "Stop"
End If
End With
End Sub

Should work.

Andrew

[HansV]

The toggle button is nice in that it give the user more feedback - while the code is running, the button stays down - and that you don't need a separate Boolean variable - the value of the Toggle button can be used instead.

I would do the captions the other way round: make it "Stop" if the button is down (.Value = True) and "Start" when the button is up.

[Leif]

And I thought Toggle Buttons were only something that came with duffel coats <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Thanks both, I've now got TWO ways of doing exactly what I want. Am I going to have fun today...

[Leif]

OK - running with a CommandButton and making good progress, but...

If the button is clicked, I get a nice readable font (top example below), but when the focus is moved away, it gets a bit scrunched (lower example below).
Or is it just my hardware?

[HansV]

I think it is a quirk of the way Windows XP handles fonts (or perhaps Excel 2002, but that seems less likely). My test workbook displays the same behaviour as yours in Excel 2002 on Windows XP, but it doesn't in Excel 97 on Windows NT4. In Windows XP, the smoothing option in Effects (Appearance tab of Display Properties) also influences the way the button looks.

[LegareColeman]

Or, slightly simpler:

<pre>Private Sub CommandButton1_Click()
If Me.Caption = "Run" Then
Me.Caption = "Stop"
MyAction
Else
Me.Caption = "Run"
End If
End Sub
</pre>