Macro with time before run

[brettvba]

I want to open a workbook display a msgbox to ask if you want to run the macro then say after ten seconds it automatically runs the macro can this be done?

[gplhl]

On 2002-02-27 15:15, brettvba wrote:
I want to open a workbook display a msgbox to ask if you want to run the macro then say after ten seconds it automatically runs the macro can this be done?

Lookup Timer in the VBA online help.

[Damon Ostrander]

Hi BrettVBA,

I don't believe you can do this in versions of Excel earlier than 2000 because I believe the userform must be non-modal.

All you have to do is, in the userform's initialize event, use the Application object's OnTime method to schedule your macro to run 10 seconds in the future (you can look this up in the VBA helps). I believe the syntax looks like this:

Application.OnTime Now() + 10/86400, "MyMacro"

but I can't check this because I'm on a computer without VBA helps right now.

If before the 10 seconds is up, the user runs it or cancels it, the OnTime method also provides a means to cancel the future scheduled macro.

You can also put the

Unload UserForm1

operation in the macro so that it automatically gets rid of the userform. Also, as I mentioned, you must set the userform's ShowModal property to FALSE.

[brettvba]

What I ment was I have a msgbox under workbook open event and its just a msgbox no form can a macro be run without the user pressing yes or no or cancel after 10 seconds


just to speak out loud

Hi again Brett,

Sorry. You said MessageBox, but I was thinking TextBox.

No, I don't believe you can do this because the MsgBox function produces a dialog that is modal. You cannot do anything, and I don't believe even OnTime scheduled macros can run, until you respond to the MsgBox. You really have to create a custom userform that functions like a messagebox (very easy).

[Ivan F Moala]

On 2002-02-27 16:05, Anonymous wrote:
Hi again Brett,

Sorry. You said MessageBox, but I was thinking TextBox.

No, I don't believe you can do this because the MsgBox function produces a dialog that is modal. You cannot do anything, and I don't believe even OnTime scheduled macros can run, until you respond to the MsgBox. You really have to create a custom userform that functions like a messagebox (very easy).

Yes you can do this in excel2000 using the
Address of Functon and a bit of API trickery
to get a timed mesgbox.

I have made a Class module to do this if
interested....probably don't need the class
but it was a good exercise as I have noticed
the call for a Timed msgbox ie one that dismisses itself at a set time.


Ivan

[brettvba]

Yeah that would be great Ivan im running office xp so hopefully that will run cheers.

[Ivan F Moala]

Hi Bret

The Code below will give you a timed msgbox
Just use it like a normal msgbox with the typical options....only Diff is that you set the time it displays for.
Have a play with it....

Ivan

'---------------------------------------------------------------------------------------
' Module : CodeTimer
' DateTime : 12/01/02 14:33
' Author : Ivan F Moala
' Note:
' Office 97 does not support the "AddressOf" operator which is needed to tell Windows
' where our "call back" function is.
'
' Inputs :
' Outputs :
'---------------------------------------------------------------------------------------
Option Explicit
Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerfunc As Long) As Long
Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long

'==========Public Declarations ==============================
Public TimerID As Long 'Turn on and off with this ID
Public TimerActive As Boolean 'Is the timer active
Public Const tmMin As Long = 2 'Min time allowed
Public Const tmDef As Long = 5 'Default if min set low
'============================================================

Public Sub ActivateMyTimer(ByVal Sec As Long)
Sec = Sec * 1000
If TimerActive Then Call DeActivateMyTimer

On Error Resume Next
TimerID = SetTimer(0, 0, Sec, AddressOf Timer_CallBackFunction)
TimerActive = True

End Sub

Public Sub DeActivateMyTimer()
KillTimer 0, TimerID
End Sub

Sub Timer_CallBackFunction(ByVal hwnd As Long, ByVal uMsg As Long, ByVal idevent As Long, _
ByVal Systime As Long)
Application.SendKeys "~", True

If TimerActive Then Call DeActivateMyTimer
End Sub

Function TmMsgBox(sMsg As String, Btn As VbMsgBoxStyle, Optional ShowFor As Long, _
Optional sTitle As String) As VbMsgBoxResult

'If no Title then default to App Title
If sTitle = "" Then sTitle = Application.Name
'If showfor < minimum time then set to default
If ShowFor < tmMin Then ShowFor = tmDef

'Call Timer
ActivateMyTimer ShowFor
TmMsgBox = MsgBox(sMsg, Btn, sTitle)
DeActivateMyTimer

End Function

Sub aTest()
Dim Answer

Answer = TmMsgBox("Is this OK?", vbYesNo + vbDefaultButton1, , "Data Entry check")

'>> rest of your code if required

End Sub

[Ivan F Moala]