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
Talk To Other Members