Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Macro with time before run

  1. #1
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Chippenham, UK
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.



    Regards,

    Gary Hewitt-Long

  3. #3
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Guest

    Default

    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).

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  8. #8
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  9. #9
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •