User Form Application.Wait

[cfoye130]

So I am trying to bring up a userform on open of a workbook that asks if the user wants to run the macro or not.

Pretty easy concept.

However, my issue is that I need this workbook to run automated via windows scheduler. The implications of that are that if this userform displays for ~30 seconds I want it to go away and set a cell that the macro triggers off of to Yes. So.... I thought of this simple code.

Private Sub NoConfirm_Click()
  Sheet1.Cells(1, 1) = "No"
  Unload Me
End Sub
 
Private Sub UserForm_Activate()
  Application.Wait = (Now + TimeValue("0:00:30"))
  Sheet1.Cells(1, 1) = "Yes"
  Unload Me
End Sub
 
Private Sub YesConfirm_Click()
  Sheet1.Cells(1, 1) = "Yes"
  Unload Me
End Sub

My thought being that if the user does not select yes or no within 30 seconds the form unloads and sets the value automatically. Obviously this doesnt work because as far as I know one can't run 2 macros at the same time which would be what was occuring here if the user clicked yes or no during the "wait."

How could I accomplish this? Any help is greatly appreciated!

Thank you!

 

[Damon Ostrander]

Hi cfoye130,

Use the Application.OnTime method rather than Application.Wait. This way you will be able to do other things and other macros can run until being interrupted when your .OnTime code runs. Be sure to check whether cell A1 has been already set to "No" before setting it to "Yes".

Damon