Macro with time before run

brettvba

MrExcel MVP
Joined
Feb 18, 2002
Messages
1,030
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?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top