Consulting

Results 1 to 5 of 5

Thread: How to create alert for streaming data

  1. #1

    How to create alert for streaming data

    I have data coming into Excel that updates every minute, by creating a new cell for each new data point.
    So, row adds upon a row with each data point.
    My question, how do I create an alert in Excel (audio or by SMS preferrably) whenever a value above a certain number appears in one of the newly added cells.
    I have Excel 2019.
    Would very much appreciate your help :-)
    Thank you!

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,942
    Location
    I am not sure if that would trigger a change event. If it does:
    1. Put first into the worksheet, right click sheet tab, View Code, and paste.
    2. Put 2nd into a Module. Add the Outlook object in Tools > References.
    3. Enter value greater than 100 in column A to trigger the event.
    4. Change max value and column to suit in (1).
    5. Uncomment MailIt line in (1) and add your SMS info for your cell's carrier.
    6. Uncomment Exit Function line in (1) if you want all changes that might occur at once.

    Sheet Code:
    Private Sub Worksheet_Change(ByVal Target As Range)  Dim r As Range, c As Range, calc As Integer, max As Long
      
       max = 100
      Set r = Intersect(Target, Range("A1", Cells(Rows.Count, "A").End(xlUp)))
      If r Is Nothing Then Exit Sub
      
      With Application
        .ScreenUpdating = False
        .EnableEvents = False
        calc = .Calculation
        .Calculation = xlCalculationManual
      End With
      
      For Each c In r
        If c.Value > max Then _
          Application.Speech.Speak c.Value & " exceeded the maximum of " & max & " in cell " & c.Address(False, False) & ".", True
          'MailIt "5555555555@vtext.com", "Exceeded Max", "Cell " & c.Address(False, False) & " value was " & c & "."
          GoTo EndNow
      Next c
      
    EndNow:
      With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = calc
      End With
    End Sub
    Module code:
    Sub MailIt(sEmail As String, sSubject As String, sBody As String)    Dim oMailItem As Object, oRecipient As Object
        Dim OLApp As Outlook.Application
        Dim NewTask As Outlook.TaskItem
        
        ' Set the Application object
        Set OLApp = New Outlook.Application
        Set oMailItem = OLApp.CreateItem(0)
        Set oRecipient = oMailItem.Recipients.Add(sEmail)
        oRecipient.Type = 1
        With oMailItem
            .Subject = sSubject
            .Body = sBody
            .Display 'To test, uncomment and comment next line
            '.Send
        End With
    End Sub

  3. #3
    Ok, I will try tonight, thank you!!! Will let you know how it works.

  4. #4
    How do i "uncomment" this line and enter my phone number properly to receive the SMS alerts?
    Did I do below correctly for the US number? Or I need to enter (+1)? Do I keep the quotations marks?

    'MailIt "212-212-3333", "Exceeded Max", "Cell " & c.Address(False, False) & " value was " & c & "."

  5. #5
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,942
    Location
    Uncomment the line like any by removing the first character, single quote.

    Have you not used SMS via your email application like Outlook? That is the sEmail value that you enter. Do it manually first.

    I used an example for USA Verizon cell carrier. If you do not know, you can lookup SMS syntax if you know the phone number and country. Had your example been for USA Verizon, 2122123333@vtext.com.

    I only listed this one. There are others. Here is a "free" carrier lookup service. https://freecarrierlookup.com/
    For a fee, they have bulk and API lookups too.

Tags for this Thread

Posting Permissions

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