Message Pop-Up Alert Window Needed in Excel

Alber36

New Member
Joined
Sep 14, 2005
Messages
8
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)
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Ok. Here's what I've come up with. It's a pretty simplistic solution, but it may well work. I've assumed that Cell B1 shows the target price and cell C1 shows the current price.

In cell A1 on the worksheet, I entered the following:

=IF(B1>=C1,"YES","NO")

Now, in the ThisWorkbork section of the VBA window, enter the following:

Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)

If ActiveSheet.Range("A1") = "YES" Then
MsgBox "IBM Has met its target price of $90"
End If

End Sub


The only problem is that the message appears every time the price changes when the formula in A1 is valid.

I hope this helps you a little bit, or at least gives you some ideas!

Tim
 
Upvote 0
Okay, getting closer.

It seems that this code is only making the pop-up box appear when i click around somewhere on the active worksheet, but if I have Excel open in the background (while using another program), the message window will not pop up once cell A1 changes from "NO" to "YES"- which is when i need the window to pop up, and for Excel to flash on the taskbar.

Thanks!
 
Upvote 0
I think this code will do what you want. I've set it up so that the alert provides the stock symbol and the target price for the particular stock that has met its target value. The alert will only occur the first time the stock meets its target, not with each change. If the current price drops below its target and then achieves it again, the alert will occur again. Hope this helps.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Static iTest() As Integer
Dim L As Long, lLast As Long

lLast = Cells(65536, 1).End(xlUp).Row
ReDim Preserve iTest(1 To lLast)

L = Target.Row
If Target.Value < Cells(L, 2) Then iTest(L) = 0
If Target.Value >= Cells(L, 2).Value Then iTest(L) = iTest(L) + 1
If iTest(L) = 1 Then MsgBox Cells(L, 1) & " has met its target price of $" & CStr(Cells(L, 2).Value)

End Sub
 
Upvote 0
Try changing the name of the VBA macro to:

Sub Test()

If ActiveSheet.Range("A1") = "YES" Then
MsgBox "IBM Has met its target price of $90"
End If

End Sub

It was probably because of the heading I put it under. It worked for me when I changed the value in the current price cell, though. I assumed that the values changing constantly would trigger the event and make the script run...
 
Upvote 0
Thanks all for the help.

I have entered Paul's above code, but for some reason I am getting no action, no matter where the prices jump around. Perhaps, I have entered the code in the wrong location in VB Editor? I inserted the code under the General section.
 
Upvote 0
The code should be in the ThisWorkbook object. Its an event procedure which is activated when a value on any worksheet in the workbook changes. I hope that helps.
 
Upvote 0
Paul,

This is almost perfect! If I manually change the price in the cell, then this code works, however, the only issue is that these prices are changing real-time from an external data source using a constant formula in the cell. I think this code is not recognizing the change in cell value as a change in the workbook (bc the formula is not changing), and so is not re-computing when the price changes.

Perhaps what I need to do is to have a 4th column that states "YES" if the 'current price' less the 'target price' is a positive and "NO" if the 'current price' less the 'target price' is a negative, and have the code work off of that column. Although, this may have the same issue because again, there are no actual changes in formula...

As an alternative, is there a way the code can be written so that the calculation is performed every, say, 10 seconds as opposed to in the event of a workbook change?

Thanks, Alberto
 
Upvote 0
I found some code that supposedly makes the Excel icon in the taskbar flash but have not been able to get it to work. Anyone have any ideas why?
When I step thru code for "With udtFWInfo" the properties (cbSize, hWnd, etc) all take on appropriate vales however when I execute the End With they all lose their values.

Any help would be appreciated. Thanks




Code:
Option Explicit

Private Type FLASHWINFO
  cbSize As Long
  Hwnd As Long
  dwFlags As Long
  uCount As Long
  dwTimeout As Long
End Type

'Stop flashing. The system restores the
'window to its original state
Private Const FLASHW_STOP As Long = 0

'Flash the window caption
Private Const FLASHW_CAPTION As Long = &H1

'Flash the taskbar button
Private Const FLASHW_TRAY As Long = &H2

'Flash both the window caption and taskbar button
Private Const FLASHW_ALL As Long = (FLASHW_CAPTION Or FLASHW_TRAY)

'Flash continuously, until the FLASHW_STOP flag is set
Private Const FLASHW_TIMER As Long = &H4

'Flash continuously until the window comes
'to the foreground
Private Const FLASHW_TIMERNOFG As Long = &HC

Private FLASHW_FLAGS As Long



Private Declare Function LoadLibrary Lib "kernel32" _
  Alias "LoadLibraryA" (ByVal lpLibFileName As String) As Long

Private Declare Function GetProcAddress Lib "kernel32" _
  (ByVal hModule As Long, ByVal lpProcName As String) As Long


Private Declare Function FreeLibrary Lib "kernel32" _
  (ByVal hLibModule As Long) As Long

Private Declare Function FlashWindowEx Lib "user32" _
   (FWInfo As FLASHWINFO) As Boolean
   
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, _
     ByVal lpWindowName As String) As Long

Private Function APIFunctionPresent(ByVal FunctionName _
   As String, ByVal DllName As String) As Boolean

    Dim lHandle As Long
    Dim lAddr  As Long

    lHandle = LoadLibrary(DllName)
    If lHandle <> 0 Then
        lAddr = GetProcAddress(lHandle, FunctionName)
        FreeLibrary lHandle
    End If
    
    APIFunctionPresent = (lAddr <> 0)

End Function


Sub FlashExcelInTaskbar()
Const NumberOfFlashes = 5
Dim udtFWInfo As FLASHWINFO
    
    Application.Wait Now + TimeValue("00:00:05")
    'Prevent Errors by checking if
    'the API function is available on the Current OS
    If Not APIFunctionPresent("FlashWindowEx", "user32") _
       Then Exit Sub
       
    With udtFWInfo
       .cbSize = Len(udtFWInfo)
       .Hwnd = Application.Hwnd
       .dwFlags = FLASHW_FLAGS Or FLASHW_TRAY
       .uCount = NumberOfFlashes 'flash window 5 times
       .dwTimeout = 0
    End With
    
    Call FlashWindowEx(udtFWInfo)
End Sub
[/code]
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top