Excel Macro. User gets hour glass cursor while macro runs

[GCRanger]

I have a macro which waits for a minute and then runs again. The wait is done using this code:
Dim dtNextRunAt As Date
Dim strStopTimer As String

dtNextRunAt = Now() + TimeSerial(0, 1, 0)
Do While dtNextRunAt > Now()
'do something
Loop

While the do loop is running the user just sees an hourglass and cannot click in the worksheet, minimize or even close excel.
How can I run the macro and still allow the users to minimize excel or click a button to stop the macro?

Thanks

 

[Kryzsoccer]

Without actually seeing your code I'm not sure if this will work, but why dont you give this a try:

Dim dtNextRunAt As Date
Dim strStopTimer As String

dtNextRunAt = Now() + TimeSerial(0, 1, 0)
Do While dtNextRunAt > Now()
DoEvents
'do something
Loop

 

[TonyJollans]

Hi GCRanger,

Use the OnTime Method. The system takes care of when it runs so you don't have a processor loop:

Sub Sched()

Dim dtNextRunAt As Date
Dim strStopTimer As String

dtNextRunAt = Now() + TimeSerial(0, 1, 0)
Application.OnTime dtNextRunAt, "Sched"

End Sub

Enjoy,
Tony

 

[GCRanger]

Thanks kryzsoccer. The DoEvents worked.

Tony, I also tried the OnTime method but it just jumped past it to the next bit of code. I'm probably doing something wrong. Thanks for the info though it could come in handy down the road.

 

[TonyJollans]

Hi GCRanger,

Exactly the point. The statement schedules the procedure to rerun at whatever point in the future you want it to. The system takes care of how it's done without a processor-bound loop.

Enjoy,
Tony