Results 1 to 2 of 2
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    New York, New York, Lebanon
    Thanked 1 Time in 1 Post

    Start/Stop Button (Excel 2K and >)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Loungers

    What is the best way to generate a Start Processing / Stop Processing command button on a User Form?

    What I want to do is:

    1) Display a Userform with a command button Labeled Start Processing.
    2) The user will press this button, and the Click event of the button fires and changed the caption to Stop Processing and execute some code.
    3) When the user clicks again, I would like the code to halt, or break at the line it was on when the button was pressed.

    Essentially I want to programmatically place a break point at some arbitrary line, and stop the code. I am thinking of a If Then End If statement with a stop command in it, but how do I check when the button is pressed after the code has started?

    Definitely there will be some code to change the caption back to Start Processing again and the code will need to continue.

    Thanks in advance.

    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Start/Stop Button (Excel 2K and >)

    Declare a module-level variable at the top of the module behind the userform:

    Private blnContinue As Boolean

    (If you want to call code defined outside the userform module, make it a Public declaration in a standard module instead)

    Make the On Click code of the command button look like this:

    Private Sub cmdButton_Click()
    If blnContinue = False Then
    blnContinue = True
    Me.cmdButton.Caption = "Stop Processing"
    ' Call procedure to be executed
    blnContinue = False
    Me.cmdButton.Caption = "Start Processing"
    End If
    End Sub

    The procedure DoSomething should check if blnContinue has become false:

    Private Sub DoSomething()
    On Error GoTo ErrHandler


    ' Give events a chance
    If blnContinue = False Then
    GoTo ExitHandler
    End If


    ' If necessary, clean up here
    Exit Sub

    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

Posting Permissions

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