Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

VBA Visual Basic for Applications (Microsoft) FAQ

VBA How To

Show a Message Box for a specified time. by PBAPaul
Posted: 13 Apr 04

One often wishes to show a message box to a user to inform them that a particular operation has completed. Sometimes there are further operations to complete, but these will not start until the user has closed the message box.

This technique creates a message box (actually a UserForm) and shows it for a specified time. After that specified time, the message box will close and allow the VBA to continue.
First create a User Form named FlashForm. The form should have 2 labels on the form named MainLabel and TimedLabel and a command button named OKButton.

The UserForm_Activate code (listed below) will set the labels etc to the right size and position.

Copy the code below to the user form code sheet.

Private Sub OKButton_Click()
End Sub

Private Sub UserForm_Activate()
    With Me
        .Height = 190
        .Width = 230
        .MainLabel.Height = 40
        .MainLabel.Width = 210
        .MainLabel.Left = 10
        .MainLabel.Top = 12
        .TimeLabel.Height = 24
        .TimeLabel.Width = 210
        .TimeLabel.Left = 10
        .TimeLabel.Top = 66
        .OKButton.Height = 36
        .OKButton.Width = 210
        .OKButton.Left = 10
        .OKButton.Top = 102
    End With
    TimeSet = Now + TimeValue("00:00:" & Format(T, "00"))
    Application.OnTime TimeSet, "ForceOK"
End Sub

Now copy the code below to a standard module.

Public TimeSet As Variant
Public OKClicked As Boolean
Public T As Integer

Sub ShowQuickMessage()
    ShowFlashForm "Sheet 1 has now printed", 10
End Sub

Sub ShowFlashForm(Title As String, Time As Integer)
    Load FlashForm
    T = Time
    With FlashForm
        .TimeLabel.Caption = "This display will disappear in " & T & " seconds."
        .Caption = "INFORMATION MESSAGE"
        .MainLabel.Caption = Title
    End With
    Unload FlashForm
    OKClicked = False
End Sub

Sub ForceOK()
    On Error Resume Next
    With FlashForm
    End With
    Application.OnTime TimeSet, "ForceOK", , False
    OKClicked = True
    Unload FlashForm
End Sub

The principle behind this is that you use the code
ShowFlashForm "Sheet 1 has now printed", 10
to show the Flashform with the MainLabel showing the text for 10 seconds.

The FlashForm is activated and as part of that activation it starts the OnTime function. This means that at the TimeSet , which is T seconds after the form was activated, then it will call the macro ForceOK which is also called by clicking the OK button.

This macro closes the FlashForm and stops the OnTime function.

I use this technique in a macro as follows:

Sub MyUse()
   Backup C: - a routine that takes a few minutes
   ShowFlashForm "C backed up", 5
   Backup D: - a second routine that takes a few minutes
   ShowFlashForm "Both drives backed up", 10
End Sub


Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close