Timed text box

[tcebob]

I'd like to have a text box appear after an event but only last for several seconds -- maybe 20. Just to verify that the event took place.

 

[DanteAmor]

It is an example, because there are several events that can occur on a sheet and you did not mention exactly which event.
Do the following:


1. Create a userform1 with a Label1
2. In the userform1, put the following code:

Private Sub UserForm_Activate()
    Label1.Caption = "The event took place"
    Application.OnTime Now + TimeValue("00:00:05"), "Close_Msg"
End Sub

INSERT A USERFORM
Press Alt-F11 to open the VBA editor. From the menu select Insert > Userform


3. In the events of your sheet, put the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Address(False, False) = "C3" Then
        UserForm1.Show
    End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.


4. Create a module and put the following code:

Sub Close_Msg()
    unload UserForm1
End Sub

INSERT A MODULE
Press Alt-F11 to open the VBA editor. From the menu select Insert > Module. On the sheet that opens, paste the code previous.

-----


It works in the following way, when you select cell C3 the userform will open automatically and it will close after 5 seconds.

 

[tcebob]

More complex than I had hoped but I'll give it a go tomorrow. Thanks for the reply. The event was a save to .pdf.

 

[DanteAmor]

More complex than I had hoped but I'll give it a go tomorrow. Thanks for the reply. The event was a save to .pdf.

Your requirement is not simple.
If you only want a message after saving the pdf, then a msgbox appears and you close it with a click.

Sub Macro8()
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & "file.pdf"
    MsgBox "The event took place"
End Sub

 

[Rick Rothstein]

If I remember correctly, some people (quite a while ago now) said they had problems with the solution below, but I have never experience a problem with it myself. This single line of code will display a MessageBox for approximately 5 seconds (change the 5 for more or less display duration) after which code execution will continue. Of course, the user can dismiss the MessageBox earlier if desired.

CreateObject("WScript.Shell").PopUp "Your Message", [B][COLOR="#0000FF"]5[/COLOR][/B]

See this link for complete information about the various options available with this MessageBox...

http://msdn.microsoft.com/en-us/library/x83z1d9f(v=vs.85).aspx

 

[DanteAmor]

If I remember correctly, some people (quite a while ago now) said they had problems with the solution below, but I have never experience a problem with it myself. This single line of code will display a MessageBox for approximately 5 seconds (change the 5 for more or less display duration) after which code execution will continue. Of course, the user can dismiss the MessageBox earlier if desired.

CreateObject("WScript.Shell").PopUp "Your Message", [B][COLOR=#0000FF]5[/COLOR][/B]

See this link for complete information about the various options available with this MessageBox...

http://msdn.microsoft.com/en-us/library/x83z1d9f(v=vs.85).aspx

Thanks Rick, (saving the day). Works well in excel 2007

 

[tcebob]

Rick, I tried your suggestion, using "CreateObject("WScript.Shell").PopUp "Saved to Desktop", 5". The box appeared but did not turn itself off. I note that the Pop up method page is dated 2011. I am using Excel version 1905 so there may have been changes in the last 8 years.

 

[DanteAmor]

Rick, I tried your suggestion, using "CreateObject("WScript.Shell").PopUp "Saved to Desktop", 5". The box appeared but did not turn itself off. I note that the Pop up method page is dated 2011. I am using Excel version 1905 so there may have been changes in the last 8 years.

As Rick commented, some people had problems.
Try my solution.