Create a 5 second message box that shows text in a cell

[mrlanier]

Is there a macro that will create a 5 second message box pop-up showing
text contained in Sheet1, B1 when Sheet1,A1=1? Both the values of A1
will change as will the text in B1. The box will need to open as often
as the value in A1 changes. Keep in mind I am using a "splash" which
may or may not inhibit a box from opening. Is such a request possible?
Any suggestions are much appreciated.

Michael

 

[Nigel]

Create a user form adding a label to display the text message. I called the
form UserForm1 and the text label Label

Then add this code to the worksheet where you are monitoring the value of A1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And Range("A1") = 1 Then
With UserForm1
.Label.Caption = Range("B1")
.Show (0)
End With
Application.OnTime Now + TimeValue("00:00:05"), "MsgClose"
End If
End Sub

Add this code to a general module.....

Private Sub MsgClose()
Unload UserForm1
End Sub

 

[Bob Phillips]

Dim cTime As Long
Dim WSH As Object

Set WSH = CreateObject("WScript.Shell")
cTime = 10 ' 10 secs
Select Case WSH.Popup("Open an Excel file?!", cTime, "Question",
vbOKCancel)
Case vbOK
MsgBox "You clicked OK"
Case vbCancel
MsgBox "You clicked Cancel"
Case -1
MsgBox "Timed out"
Case Else
End Select


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Joergen Bondesen]

Hi Bob

When I am running below, the Popup do not disappear after 1 sec.
Have I misunderstod something?
I am using Excel 2003 with Windows XP, both is UK.

Option Explicit

Sub test()
Dim cTime As Long
Dim WSH As Object

Set WSH = CreateObject("WScript.Shell")
cTime = 1 ' 10 secs
Select Case WSH.Popup("Open an Excel file?!", _
cTime, "Question", vbOKCancel)
Case vbOK
MsgBox "You clicked OK"
Case vbCancel
MsgBox "You clicked Cancel"
Case -1
MsgBox "Timed out"
Case Else
End Select
End Sub

 

[Jim Cone]

Joergen,
I am not Bob (obviously), but I found that you could not depend on
the WScriptShell message box to always appear/disappear as wanted.

I think a better method is Nigel's approach that times a form.
Although, I would start the OnTime method before showing the form.
In a standard module...

Sub TellThemAboutIt()
Application.OnTime Now + TimeValue("00:00:02"), "MsgClose"
UserForm1.Show
End Sub

'The user could close the form before the time elapsed.
Private Sub MsgClose()
Dim N As Long
N = UserForms.Count
If N > 0 Then
Unload UserForms(N - 1)
End If
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Joergen Bondesen"
<[email protected]>
wrote in message
Hi Bob
When I am running below, the Popup do not disappear after 1 sec.
Have I misunderstod something?
I am using Excel 2003 with Windows XP, both is UK.

Option Explicit
Sub test()
Dim cTime As Long
Dim WSH As Object

Set WSH = CreateObject("WScript.Shell")
cTime = 1 ' 10 secs
Select Case WSH.Popup("Open an Excel file?!", _
cTime, "Question", vbOKCancel)
Case vbOK
MsgBox "You clicked OK"
Case vbCancel
MsgBox "You clicked Cancel"
Case -1
MsgBox "Timed out"
Case Else
End Select
End Sub

 

[mrlanier]

Hello again,

I'm the person that began this topic. I've attempted to use Nigel's
suggestion. For the most part, I believe I set up everything correctly
but the red
text in both Sheet1 and Module1 point to something incorrect in my
UserForm1. In the UserForm1 box, using the Toolbox, I placed a Label
section and erased the Label1 text within it. Double clicking on the
box, the UserForm code reads:

Private Sub UserForm_Click()
End Sub

I feel I'm missing something at this point. As directed, the
Module1 entry reads:

Private Sub MsgClose()
Unload UserForm1
End Sub

The bulk of Nigel's formula was entered into Sheet1. Where am I going
wrong? Thanks.

Michael

 

[Bob Phillips]

Two things.

the close code

Private Sub MsgClose()
Unload UserForm1
End Sub

should be in a general code module, not the sheet module.

Also, Nigel caled the label Label in his code, but adding a label names it
Label1. Make sure yours is correct.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[mrlanier]

Nigel,

Thanks very much. I finally got it to work and it works exactly
according to need.

Michael

 

[mrlanier]

Thanks Bob. Much appreciated.

Michael

 

[Joergen Bondesen]

Hi Jim.

Thanks.