How to wait and check for user input?

[Norm]

From an Excel macro, is there a way to pause for some time
and check for user input? If no input then the macro
proceeds.

Example: macro is running and gets to a point where it
stops and waits for 10 seconds. User can enter something
or click something - it doesn't matter, whatever is
easier. But if there's no response then the macro
continues.

Thank You!

 

[Frank Kabel]

Hi
directly from the Excel help file:

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

 

[Guest]

But this doesn't allow the user to do anything. Is there
a way to pause and allow for a user response. If no
response, then the macro will do something different.

Norm

 

[Bob Phillips]

Hi Norm,

Here is an idea.

Build a simple userform, say with a textbox in it. Put this code in the
userform initialize event

Private Sub UserForm_Initialize()
Application.OnTime Now + TimeSerial(0, 0, 10), "CloseForm"
End Sub


Add this procedure to a standard code module

Public UserInput

Public Sub CloseForm()
UserInput = Userform1.Textbox.Text
Unload UserForm1
End Sub

and in your macro do something like

'initial code
Userfoorm1.Show
MsgBox UserInput
' rest of your code
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)