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()
    ForceOK
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
        .Show
    End With
    Unload FlashForm
    OKClicked = False
End Sub

Sub ForceOK()
    On Error Resume Next
    With FlashForm
        .Hide
    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

Paul

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

My Archive

My FAQ Archive

Come Join Us!

Posting Guidelines

Jobs

VBA Visual Basic for Applications (Microsoft) FAQ

VBA How To

My Archive

Join Tek-Tips® Today!

Join Tek-Tips^® Today!