CODEWITHSUNDEEP
formsexcelvbaresumeonpause
user1143529
user1143529

Reputation: 53

Pause and Resume Buttons in VBA

I have a VBA subprogram which draws some shapes in spreadsheet and moves them around. This is what my subprogram called

Sub Assignment(amplitude As Double, deltaphase As Double)

I take my amplitude and deltaphase values from a form. There are three button in my form "Start, Pause, Resume".

I know how to code the "start" button.

Private Sub start_Click()

Dim formAmplitude As Double
Dim formPhase As Double

formAmplitude = assignmentform.ampbox.value
formPhase = assignmentform.phsbox.value

Call Assignment(formAmplitude, formPhase)

End Sub

But I don't know what to do for "Pause" and "Resume" buttons...

Any help will be appreciated

Upvotes: 0

Views: 4575

Answers (1)

Jon Fournier
Jon Fournier

Reputation: 4327

What I usually do is tell the equivalent of the Assignment routine what user form is visible to the user, so the routine periodically (like at the end of a loop/update) does a DoEvents which lets the button click event process on the form. After the DoEvents call in Assignment, you'd then check a boolean flag on the form, like "Running", and if that's false you don't proceed. The "Pause" button click event would have to set this Running flag to false, of course.

You really should have the Initialization portion of the code run in a separate function than the execution portion, so that you can initialize your animation, open the form up, then call the execution code, which would resume any animation when the user clicks "Play".

I hope that makes sense, because otherwise you'd have to have a loop like this:

Do While Frm.Running = False
    DoEvents
    Sleep 100
Loop

You'd have to do that anywhere you want to check for a pause/resume click. The DoEvents call lets Excel not freeze up in the loop that's going to run until you click Resume, and the sleep is to keep your CPU utilization from spiking up. You'd have to use the appropriate Sleep Windows API call for that.

Upvotes: 0

Related Questions