Results 1 to 12 of 12
  1. #1
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Loop-de-loop (2000/2002)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop-de-loop (2000/2002)

    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

  3. #3
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop-de-loop (2000/2002)

    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?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop-de-loop (2000/2002)

    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?

  5. #5
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop-de-loop (2000/2002)

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop-de-loop (2000/2002)

    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.

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Loop-de-loop (2000/2002)

    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop-de-loop (2000/2002)

    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.

  9. #9
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop-de-loop (2000/2002)

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

  10. #10
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop-de-loop (2000/2002)

    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?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop-de-loop (2000/2002)

    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.

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop-de-loop (2000/2002)

    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>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •