Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Message Pop-Up Alert Window Needed in Excel

This is a discussion on Message Pop-Up Alert Window Needed in Excel within the Excel Questions forums, part of the Question Forums category; I have a simple spreadsheet with three columns- in column A, I have a listing of stock symbols. In column ...

  1. #1
    New Member
    Join Date
    Sep 2005
    Location
    New York
    Posts
    8

    Default Message Pop-Up Alert Window Needed in Excel

    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)

  2. #2
    Board Regular j844929's Avatar
    Join Date
    Aug 2002
    Location
    Being a frog...a pond...
    Posts
    426

    Default

    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
    It's an accent, not a speech impediment...

  3. #3
    New Member
    Join Date
    Sep 2005
    Location
    New York
    Posts
    8

    Default

    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!

  4. #4
    Board Regular
    Join Date
    Aug 2005
    Location
    California
    Posts
    321

    Default

    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
    Paul

  5. #5
    Board Regular j844929's Avatar
    Join Date
    Aug 2002
    Location
    Being a frog...a pond...
    Posts
    426

    Default

    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...
    It's an accent, not a speech impediment...

  6. #6
    New Member
    Join Date
    Sep 2005
    Location
    New York
    Posts
    8

    Default

    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.

  7. #7
    Board Regular
    Join Date
    Aug 2005
    Location
    California
    Posts
    321

    Default

    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.
    Paul

  8. #8
    New Member
    Join Date
    Sep 2005
    Location
    New York
    Posts
    8

    Default

    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

  9. #9
    Board Regular btadams's Avatar
    Join Date
    Jan 2003
    Location
    Richmond, Va
    Posts
    1,874

    Default

    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]
    “The early bird gets the worm, but the second mouse gets the cheese.”

    Put Yourself on the Excel Map:
    http://www.mapservices.org/myguestmap/map/MrExcel

    Instructions for Map:
    http://www.mrexcel.com/map.html

  10. #10
    Board Regular
    Join Date
    Aug 2005
    Location
    California
    Posts
    321

    Default

    Alberto,

    I'm trying some experiments. I'll get back to you.

    Paul
    Paul

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com