Excel VBA - Timeout a messagebox 2

[Callan101]

I'm writing something in Excel 97 VBA which needs a response from a messagebox within a certain time. An item is shown and the user has to click "Yes" or "No" within five seconds. After they click Yes or No it records their response and the time taken and moves on to the next item, or after five secons it records a nil response and moves on by itself.

(so the sequence is: item appears, timer starts, messagebox appears, user clicks Y/N, messagebox closes, time since appeareance is noted, response and time taken are recorded, next item appears.)

Trouble is that if there is no reponse within 5 seconds it needs to move on automatically, and I can't get the messagebox to timeout. The rest works fine but the messagebox takes control until it gets a response. It doesn't check the loop timer until after the reponse, ie the messagebox is closed and relinquishes control to the loop, so I can't get the loop to check that it has reached 5 seconds.

I thought about the user simply putting Y or N in a text box, but hit a similar problem - it will not allow a change to the text box until the loop is finished.

Anyone got any suggestions?

Thanks

 

[AndyCoxUK]

Why not create your own form with yes/no buttons on which includes a timer. This can be shown modally so that the user has to "answer" it before continueing, the timer can close the form if no action after 5 seconds. This form could be set to return a value for yes, no or no answer (e.g. public variable)

 

[xlhelp]

Please look at MS article 181934.

Also, take a look at

http://www.mvps.org/vb/code/TimedMsg.zip

"Mind is like a parachute. Works well when opened."

 

[combo]

With pure excel 'OnTime' method:

a UserForm1 with two buttons (yes and no):
[tt]Private Sub CommandButton1_Click()
Answer = "myYes"
Unload Me
End Sub

Private Sub CommandButton2_Click()
Answer = "myNo"
Unload Me
End Sub[/tt]

Standard module:
[tt]Public Answer As String
Public time_x As Double

Sub counter_5()
Unload UserForm1
End Sub

Sub AskQuestion()
' this can be in a loop in a module or other user form
On Error Resume Next
Answer = "myNoAnswer"
time_x = Now + TimeValue("00:00:05&quot
Application.OnTime EarliestTime:=time_x, Procedure:="counter_5", Schedule:=True
UserForm1.Show
Application.OnTime EarliestTime:=time_x, Procedure:="counter_5", Schedule:=False
MsgBox Answer
End Sub[/tt]

combo

 

[Callan101]

Thanks Andy, I didn't think past using the standard messsage box to creating one of my own. And particular thanks to xlhelp and combo for taking the time to give such detailed examples.

Problem solved - cheers all!