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



<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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)
>
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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"
<(E-Mail Removed)>
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)


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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"
> <(E-Mail Removed)>
> 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