Macro resume based on user condition
2

Question

Macro resume based on user condition

HarlanJ (Industrial)

(OP)

13 Feb 08 22:42

I've written a macro that parses text files into memory, modifies the values, and then outputs the new values to a spreadsheet. I have it written to cycle through a set number of lines in a text file before outputting the data, pausing for a moment (ex. 5 seconds), and repeating the process. However, the end user will need to pause the execution indefinately from time to time and this is achieved by a button and textbox at the moment.

I would like to forego the textbox and have the code pause until a second button is clicked, allowing the macro to resume parsing the text file and updating the data.

Here is the current setup:

CODE

                    If Pause = True Then 'if the user wants to pause the output a message box notifies them and asks to continue
                        Sheets("Start Page").PauseLabel.Caption = "Paused"
'                        While timer < Wait + 2.05  'time in seconds to wait while the "Paused" Label is updated
'                            DoEvents  'do nothing
'                        Wend

                        Call UserToldProgramToWait

                        If MsgBox("Paused. Continue?", vbYesNo, "Dryer Data Extraction Tool") = vbNo Then
                            Close #1
                            Pause = False 'The user decided to quit, so the program no longer needs to be paused
                            Sheets("Start Page").PauseLabel.Caption = ""
                            Exit Sub
                        End If
                        Pause = False 'the user decided to continue, so the program is no longer paused
                        Sheets("Start Page").PauseLabel.Caption = ""
                    End If

"Pause" is a boolean public variable updated when a command button on the active worksheet is clicked (changes to "true"). A textbox is displayed, notifying the user that the macro has been paused, and execution stops until the user closes it.

"UserToldProgramToWait" is the subroutine I'm playing with that would ideally hold execution indefinately like the text box.

CODE

Public Sub UserToldProgramToWait()

    Dim Wait As Double

    Wait = timer
    Do Until UserWantsToResume = True 'Until the user clicks the resume button the program will run this loop and pause for 5 seconds before checking again.
        While timer < Wait + 10 'wait 10 seconds before checking again
            DoEvents 'Do nothing
        Wend
    Loop
    UserWantsToResume = False 'Right after the user decides to resume code execution, This variable is reset until we need to wait again.

End Sub

After the user clicks the Resume button on the worksheet, "UserWantsToResume" is then True and the code continues as normal. A side effect of this method is that Excel will fail to respond after 10-15 seconds. Otherwise, this works fine.

I'm guessing Windows XP determines that a program is failing to respond if it continues to execute the same code without change for more than 15 seconds. I have no problems breaking the operation with the Escape key after Excel 'Stops Responding'.

Any ideas?

HarlanJ (Industrial) · Answer 1 · 2008-02-15 18:36:21

RE: Macro resume based on user condition

HarlanJ (Industrial)

(OP)

15 Feb 08 18:36

Cross-posted here: Mr. Excel Forums

handleman (Automotive) · Answer 2 · 2008-02-15 21:37:00

The best way to do this would be to make execution actually stop. You would probably have to do quite a bit of modification to your code, but you would keep track of where you are in the file and have your button actually call the routine that picks up where it stopped before.

melone (Electrical) · Answer 3 · 2008-02-15 22:29:32

Could you just make a function that displays a "Press here to continue", and only return from the function once the button has been pressed?

johnwm (Computer) · Answer 4 · 2008-02-16 05:31:38

Put a button on your form with a name 'cmd1' and a caption value of 'Pause' and try this code

CODE

Private Sub cmd1_Click()
Static OnOff As Boolean
OnOff = Not OnOff
If OnOff Then
cmd1.Caption = "Resume"
Else
cmd1.Caption = "Pause"
'running code here
'get and process next batch of lines
End If
End Sub

Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376: Eng-Tips.com Forum Policies before posting
Steam Engine enthusiasts
Steam Engine Prints

HarlanJ (Industrial) · Answer 5 · 2008-02-28 12:04:46

Sorry for the late reply, it's been super busy here.

[bold]handleman:[/bold]

I thought about that and incorporated into into my code in a different context. The user can now pick a line in the data file to start at and the code will cycle through until it reaches that line. Execution then continues as normal. Thanks, it got me thinking.

[bold]melone:[/bold]

That was the original intent. The issue I was having is that excel would fail to respond when I trapped the execution in a seemingly endless loop using a 'wait until -> do events' method. I was trying to use a time statement to get the program to "wait" before checking an external condition again and again until it was true. I was on the right track, just in the wrong direction.

[bold]johnwm:[/bold]

Thanks for your reply. That was a different way of looking at the problem and got me thinking in a new direction. It actually helped to solve a related problem in another area of the program.

Now on to the solution:

I purchased the "Excel 2007 vba Programmer's Reference" Published by Wrox and came across a similar problem. It's close to what I was doing, but is not based on time (I'm referring to Bob Bedell's reply).

I changed the code in my previous post to this:

CODE

Public Sub UserToldProgramToWait()

    Dim Wait As Integer

    ' Check if resume button has been pressed
    Do While UserWantsToResume = False
        ' Slow the processing down a bit before trying again
        For Wait = 1 To 100
            DoEvents 'Returns control to the pc and allows the user to interact with the worksheet
        Next

        If StopAllExecution = True Then
            ThisWorkbook.Sheets(Ws.Name).PauseLabel.Caption = "Done"
            End
        End If
    Loop

    UserWantsToResume = False 'resetting the variable for the next pause cycle

End Sub

This was exactly what I was trying to do. The main procedure updates on a timer and will call this subroutine if the user clicks the pause button on the worksheet. The code will continue to execute in the background but is effectively "trapped" until the resume button is pressed (setting UserWantsToResume=true). In the meantime, they can interact with the worksheet and change values that will affect the output later on.

Thanks again for all your help, it got me off in the right direction. :)

Come Join Us!

Posting Guidelines

Macro resume based on user condition
2