Message Pop-Up Alert Window Needed in Excel

[Alber36]

I have a simple spreadsheet with three columns- in column A, I have a listing of stock symbols. In column B, I have a listing of the desired sale price of each stock (or ‘price target’). And in column C, I have the actual ‘current price’ of each stock, which contains a formula linked to an external stock pricing source. This column is real-time and is constantly updating.

The user will have multiple programs open with Excel running in the background, and what I need is the following:

When the ‘current price’ equals or is greater than the ‘price target’, then I need a message alert window to pop-up in Excel so that the user’s Excel in the taskbar flashes and the user then knows to go into Excel to view the alert. I would like for the message box to state “IBM has met its price target of $90”.

Any ideas on how to work this out would be greatly appreciated. Thanks!

(Using Excel 2003)

 

[Gates Is Antichrist]

btadams: does udtFWInfo.cbSize or just cbSize appear to lose its value? Try
?udtFWInfo.cbSize
in the immediate window after the End With.

Your question is worth a separate thread so if you want to continue I suggest that. I'll just mention a few resources for working with API/DLL calls.

Anatomy of a declare statement - NOTE! the "See also" links too!
http://msdn.microsoft.com/library/d...dcore/html/deovrAnatomyOfDeclareStatement.asp

Understanding and Using Windows API Calls for Excel Programming
http://www.awprofessional.com/articles/article.asp?p=366892&rl=1

What are APIs, why use them, how to use them, and how to find out more
www.applecore99.com/api/api001.asp

 

[btadams]

GIA,

When I step thru the With...End With and hold my cursor over the properties they all take on appropriate values (.cbSize = 20) however when the End With statement is executed all of the properties now say <Object Variable or With Block variable not set>

P.S. Thanks for the links

 

[parmel]

The problem seems to be that if there is a cell containing a formula and the value of the cell changes because of something that happens external to the workbook, no Workbook Change event occurs.

As an alternative, what I did was put the meat of the code in a module connected to a timer. The timer runs every 10 seconds and is activated when the workbook opens and is inactivated when the workbook closes. Here's the module code:

Public bCondition As Boolean

Sub Mytimer()

Dim PauseTime, Start, Finish, TotalTime
bCondition = True

Do
PauseTime = 10 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
Track
Loop Until bCondition = False
End Sub

Sub Track()
Static iTest() As Integer
Dim L As Long, lLast As Long, iTarget As Integer
Static iOld() As Integer
Static iStart As Integer
Dim iNew() As Integer
lLast = Cells(65536, 1).End(xlUp).Row
ReDim Preserve iTest(1 To lLast)
ReDim Preserve iOld(1 To lLast)
ReDim Preserve iNew(1 To lLast)

iStart = iStart + 1
If iStart = 1 Then 'get base values
For i = 1 To lLast
iOld(i) = Cells(i, 3).Value
Next i
End If

For i = 1 To lLast
iNew(i) = Cells(i, 3).Value
iTarget = Cells(i, 2)
If iNew(i) <> iOld(i) Then ' value has changed
If iNew(i) < iTarget Then iTest(i) = 0
If iNew(i) >= iTarget Then iTest(i) = iTest(i) + 1
If iTest(i) = 1 Then MsgBox Cells(i, 1) & " has met its target price of $" & CStr(Cells(i, 2).Value)
End If
Next i

End Sub


The code for the workbook open and before close events is:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
bCondition = False
End Sub

Private Sub Workbook_Open()
Mytimer
End Sub

Let me know if this works for you.