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

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


--
Cheers
Nigel



wrote in message
ups.com...
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

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)


wrote in message
ups.com...
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

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


--
Best regards
Joergen Bondesen


"Bob Phillips" wrote in message
...
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)


wrote in message
ups.com...
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

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"

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
--
Best regards
Joergen Bondesen

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

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)


wrote in message
ups.com...
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

Nigel,

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

Michael

Thanks Bob. Much appreciated.

Michael

Hi Jim.

Thanks.

--
Best regards
Joergen Bondesen


"Jim Cone" wrote in message
...
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"

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
--
Best regards
Joergen Bondesen