VBA MsgBox timer?

[Andrew Fergus]

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

 

[Tom Urtis]

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.

 

[Andrew Fergus]

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.

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.

 

[Tom Urtis]

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.

 

[Andrew Fergus]

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

 

[Future_MVP]

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,

 

[TLA]

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.

 

[KPMirror]

Andrew,

I have same problem. Any resolution to this issue?

 

[sous2817]

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.

 

[Lazi]

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