Page 1 of 2 12 LastLast
Results 1 to 10 of 20

VBA MsgBox timer?

This is a discussion on VBA MsgBox timer? within the General Excel Discussion & Other Questions forums, part of the Question Forums category; Hello Everyone Is it possible to have a timer on a VBA message box such that it disappears after a ...

  1. #1
    MrExcel MVP Andrew Fergus's Avatar
    Join Date
    Sep 2004
    Location
    New Zealand
    Posts
    5,236

    Default VBA MsgBox timer?

    Hello Everyone
    Is it possible to have a timer on a VBA message box such that it disappears after a set interval? I have a standard VBA MsgBox informing the user that an action was successful - how can I make the message box disappear (as if Ok was pressed) after 3 seconds? Is this possible? I'm new with VB and am using it in conjuntion with Access 2000.
    TIA, Andrew
    ~ >*()))><(


    I'd rather have a full bottle in front of me than a full frontal lobotomy.

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    10,984

    Default

    You probably would have received a response sooner if this was asked on the Excel Questions board, but at any rate here's one way to do what you want:

    Sub Test1()
    Dim AckTime As Integer, InfoBox As Object
    Set InfoBox = CreateObject("WScript.Shell")
    AckTime = 3
    Select Case InfoBox.Popup("Click OK or do nothing within 3 seconds.", _
    AckTime, "This is your Message Box", 0)
    Case 1, -1
    Exit Sub
    End Select
    End Sub

    Keep in mind that the first time code compiles, it can take a few extra moments, so on the first execution of this macro, it will take longer than 3 seconds for 3 seconds to elapse. Run the code twice and see if the second time around is closer to 3 seconds than the first execution, which it was for me.

  3. #3
    MrExcel MVP Andrew Fergus's Avatar
    Join Date
    Sep 2004
    Location
    New Zealand
    Posts
    5,236

    Default

    Hi Tom
    Thanks for your response. I got your code working if I created a new button that didn't do anything. If I added it to my code then I can't get it to work - the message box opens but it doesn't disappear automatically. I only made a couple of minor modifications to the message box text. I'm not sure what I have done wrong. My code is below if it is any help, and your suggestion has been incorporated towards the very bottom.
    Code:
    Private Sub DelClass_Click()
    Dim Filler1, Filler2, Message, NumRecords, Response     'Temp variables
    Filler1 = "are "                                        'Constant for MsgBox text
    Filler2 = "s"                                           'ditto
    If (IsNull([DelClassCombo])) Then                       'Nothing entered so do nothing
        DoCmd.GoToControl "DelClassCombo"
    Else
        If (DCount("[Classification_Code]", "T_Clients", "[Classification_Code] = '" & Forms![frmAdministration]![DelClassCombo] & "'")) = 0 Then
            DoCmd.SetWarnings (No)                          'No matching records so safely delete the classification
            DoCmd.OpenQuery "qryDeleteClass"
            DoCmd.SetWarnings (Yes)
        Else
            NumRecords = DCount("[Classification_Code]", "T_Clients", "[Classification_Code] = '" & Forms![frmAdministration]![DelClassCombo] & "'")
            If NumRecords = 1 Then                          'Just to get a meaningful message in the message box
                Filler1 = "is "
                Filler2 = Null
            End If
            Message = "There " & Filler1 & NumRecords & " client record" & Filler2 & " with the classification value '" & Forms![frmAdministration]![DelClassCombo] & "'.  Press the OK button to change the classification value for the affected client record" & Filler2 & " to the default classification value of '" & DLookup("[Description]", "T_Classification", "[Permanent] = Yes") & "', or press the Cancel button to exit."
            Response = MsgBox(Message, vbOKCancel + vbCriticial + vbDefaultButton2, "Are you sure you wish to proceed?")
            If Response = vbOK Then
              DoCmd.SetWarnings (No)
              DoCmd.OpenQuery "qryDeleteClass2"             'Updates selected records to the default classification
              DoCmd.OpenQuery "qryDeleteClass"
              DoCmd.SetWarnings (Yes)
            Else
                Me!DelClassCombo = Null
                Exit Sub
            End If
        End If
        Message = "The classification value of '" & Forms![frmAdministration]![DelClassCombo] & "' has been deleted."   ' changed from a MsgBox to accomodate timer per post from Tom
        Me!DelClassCombo = Null
        DoCmd.Requery ("DelClassCombo")
        DoCmd.GoToControl "DelIndustryCombo"
        Dim AckTime As Integer, InfoBox As Object               'for the auto-close MsgBox per post from Tom
        Set InfoBox = CreateObject("WScript.Shell")             'ditto
        AckTime = 3                                             'ditto
        Select Case InfoBox.PopUp(Message, AckTime, "FYI", 0)   'Tom's timer MsgBox
        Case 1, -1                                              'for Tom's timer MsgBox
        Exit Sub
        End Select
    End If
    End Sub
    Why won't this work? I initially tried setting the variables with my other variables, but that didn;t work so I then put all of your suggestion together to see if that made any difference, without success. Any thoughts?

    Thanks, Andrew

    P.S. Sorry about the code formatting - it is wrapping to the next line for longer lines and my indenting has been lost.
    ~ >*()))><(


    I'd rather have a full bottle in front of me than a full frontal lobotomy.

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    10,984

    Default

    The code I posted was meant to answer the immediate question you asked about how to compose a message box that would acknowledge itself. Given all this code now, it looks like (and I could be wrong) you have a command button named DelClass that, when clicked, does a bunch of evaluations. The first evaluation is this...

    If (IsNull([DelClassCombo])) Then
    DoCmd.GoToControl "DelClassCombo"

    ...which looks like maybe you are checking to see if a combo box named DelClassCombo has had any item selected. If not, then among other things, you want the magical message box to appear and disappear 3 seconds later.

    What I cannot determine is the possible interference given by this:
    DoCmd.GoToControl "DelClassCombo"

    Maybe the GoTo command involves something else that you either are not saying or don't know about, and maybe if a control is selected then the message box won't appear for some reason. I just cannot tell from a distance, but if you explain in words instead of code the general logic of what you are doing, a suggestion from me or someone else might be possible.

  5. #5
    MrExcel MVP Andrew Fergus's Avatar
    Join Date
    Sep 2004
    Location
    New Zealand
    Posts
    5,236

    Default

    Hi Tom

    The disappearing message box was applied to this situation as a 'proof of concept' - in other words if I can do it here, then I can use the logic in other subs, and my users can be presented with 'status updates' (i.e. informed that an action was successful) without necessarily having to click Ok all the time. I envisage changing the timer to periods both longer and shorter than 3 seconds. But firstly I want to see if it can be done (so far yes, but not for my situation).

    What does the sub do, in plain English?

    On an Access form, the user selects a 'classification' in the combo box 'DelClassCombo' and then clicks a button 'Del_Class' - to try and delete a 'classification' from a table.

    If the 'DelClassCombo' combo box is null (i.e. nothing was selected) then the sub just returns the cursor to the combo box and then does nothing - the code at the bottom is still inside the first Else statement. The message box will not appear if the first 'If' condition was met (i.e. the Null part). There are no procedures or subs attached to 'DelClassCombo' - it is just a combo box where the user selects a 'classification' from the combo box list. I could put an Exit Sub line after the
    DoCmd.GoToControl "DelClassCombo"
    line and it won't make any difference. There are no hidden subs or procedures.

    If the DelClassCombo is not null (the else condition), then it tests to see if there are any records in the many side of a one-to-many relationship with the T_Clients table (i.e. how many clients in T_Clients have the selected 'classification'). If there are no matching records then it deletes the selected 'classification' and jumps to the
    Message = "The classification value of...
    part towards the bottom, and the disappearing message box is invoked.

    If there are matching records, then the user can either do nothing (the else part where vbOK was not pressed and the sub exits), or they can delete the classification by pressing ok (the VbOk part). However, in this instance the code changes the T_Client records that were using the selected classification to a default classification value (i.e. the Dlookup...permanent = yes part). Again it proceeds to the
    Message = "The classification value of...
    part where the disappearing message box

    Everything works perfectly and I replaced a standard MsgBox with your code - whilst the sub does everything it should, the new message box deosn't disappear after 3 (or more) seconds.

    Hopefully that make sense. Thanks for looking at this.

    Andrew
    ~ >*()))><(


    I'd rather have a full bottle in front of me than a full frontal lobotomy.

  6. #6
    New Member
    Join Date
    Jan 2009
    Location
    Colorado, USA
    Posts
    5

    Default Re: VBA MsgBox timer?

    Quote Originally Posted by Andrew Fergus View Post
    Hi Tom

    The disappearing message box was applied to this situation as a 'proof of concept' - in other words if I can do it here, then I can use the logic in other subs, and my users can be presented with 'status updates' (i.e. informed that an action was successful) without necessarily having to click Ok all the time. I envisage changing the timer to periods both longer and shorter than 3 seconds. But firstly I want to see if it can be done (so far yes, but not for my situation).

    What does the sub do, in plain English?

    On an Access form, the user selects a 'classification' in the combo box 'DelClassCombo' and then clicks a button 'Del_Class' - to try and delete a 'classification' from a table.

    If the 'DelClassCombo' combo box is null (i.e. nothing was selected) then the sub just returns the cursor to the combo box and then does nothing - the code at the bottom is still inside the first Else statement. The message box will not appear if the first 'If' condition was met (i.e. the Null part). There are no procedures or subs attached to 'DelClassCombo' - it is just a combo box where the user selects a 'classification' from the combo box list. I could put an Exit Sub line after the
    DoCmd.GoToControl "DelClassCombo"
    line and it won't make any difference. There are no hidden subs or procedures.

    If the DelClassCombo is not null (the else condition), then it tests to see if there are any records in the many side of a one-to-many relationship with the T_Clients table (i.e. how many clients in T_Clients have the selected 'classification'). If there are no matching records then it deletes the selected 'classification' and jumps to the
    Message = "The classification value of...
    part towards the bottom, and the disappearing message box is invoked.

    If there are matching records, then the user can either do nothing (the else part where vbOK was not pressed and the sub exits), or they can delete the classification by pressing ok (the VbOk part). However, in this instance the code changes the T_Client records that were using the selected classification to a default classification value (i.e. the Dlookup...permanent = yes part). Again it proceeds to the
    Message = "The classification value of...
    part where the disappearing message box

    Everything works perfectly and I replaced a standard MsgBox with your code - whilst the sub does everything it should, the new message box deosn't disappear after 3 (or more) seconds.

    Hopefully that make sense. Thanks for looking at this.

    Andrew
    Andrew,

    Would you mind sending me the finished code to this question?
    I am trying to create a countdown timer in excel that is displayed in a message box and is activated by a command button.
    Thanks,

  7. #7
    TLA
    TLA is offline
    Board Regular
    Join Date
    Jul 2003
    Posts
    127

    Default Re: VBA MsgBox timer?

    I recently tried this code and the msgbox popped up fine, but never disappeared unless i clicked the button. The time part doesn't seem to work for me. I pasted teh code into a Sub in excel without adding or deleting anything.

  8. #8
    New Member
    Join Date
    May 2011
    Posts
    1

    Default Re: VBA MsgBox timer?

    [QUOTE=Future_MVP;1817065]Andrew,

    I have same problem. Any resolution to this issue?

  9. #9
    Board Regular sous2817's Avatar
    Join Date
    Feb 2008
    Location
    Raleigh, NC
    Posts
    2,260

    Default Re: VBA MsgBox timer?

    Rather than use a message box, couldn't you create your own user form that looks just like a message box and then close the user form after X number of seconds? I don't think you'll be able to do what you want with a standard message box.
    "Even a blind pig finds an acorn now and again"

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    - Search Mr. Excel using Google

    - Search Mr. Excel using Bing

  10. #10
    New Member
    Join Date
    May 2011
    Posts
    7

    Default Re: VBA MsgBox timer?

    I tried to close a Form after time_Interval , as suggested, but it would Not close either.

Page 1 of 2 12 LastLast

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
  •  


DMCA.com