MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Is there code to pause a macro?

Posted by Chris Rock on November 12, 2001 1:53 PM

I have a macro that refreshes an external data query, THEN hides some unused rows, based on the last row in the data table. The problem is, the macro is not waiting for the data to be imported before it hides rows - so it's hiding rows that shouldn't be hidden.

Is it possible, using VBA, to have the macro wait for the data to be refreshed before it does the row hiding? Can I get the macro to pause for 30 seconds - or is there another way? Perhaps a messagebox popup that tells me the refresh is complete and asks me to continue?

Posted by faster on November 12, 2001 2:09 PM

this will pause the code for 30 seconds

Sub WaitTime()
Application.Wait (Now + TimeValue("0:00:30"))
End Sub

But you may want to use something like this:
Sub WaitTimeLoop()
Application.Cursor = xlWait
Application.Wait (Now + TimeValue("0:00:05"))

If MsgBox("OK To Continue?", vbYesNo) = vbYes Then
Application.Cursor = xlDefault
Exit Do
End If
End Sub

It loops through the wait time of 5 seconds until
the user selects YES.