Results 1 to 6 of 6

Thread: value clusters with alert

  1. #1
    Neophyte vilem.iv's Avatar
    Join Date
    Dec 2020
    Posts
    3
    Articles
    0
    Excel Version
    2019

    value clusters with alert



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    i have automatically refresh pivot table when source table changes, but i am looking only for clusters of values in a row - green rounded below - image attached - I would like to "manage" Excel to create sound/email alert, when the defined value clusters apper. Has anyone knowlidge how to make it possible please?
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	excl idea.jpg 
Views:	8 
Size:	108.4 KB 
ID:	10257  

  2. #2
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    286
    Articles
    0
    Excel Version
    2007
    The following, paste into the Sheet Module, will play the TADA sound when any change is made to the sheet :

    Code:
    Option Explicit
    
    
    Private Declare Function PlaySoundA& Lib "winmm.dll" (ByVal lpszName$, ByVal hModule&, ByVal dwFlags&)
    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        'If Not Intersect([L3:L24], Target) Is Nothing Then         '<--- For specific cell/s uncomment and specify the cell/s range
            PlaySoundA "C:\windows\media\tada.wav", 0&, &H20001
        'End If
    End Sub

    This is a basic email macro :

    Code:
    Option Explicit
    
    Sub EmailSnd()
        With CreateObject("outlook.application").CreateItem(0)
            .to = "email address of recipient"
            .Subject = "test"
            .CC = "cc"
            .BCC = "bcc"
            .Body = "Hello ya all"
            .Send
        End With
    End Sub

  3. #3
    Neophyte vilem.iv's Avatar
    Join Date
    Dec 2020
    Posts
    3
    Articles
    0
    Excel Version
    2019
    Quote Originally Posted by Logit View Post
    The following, paste into the Sheet Module, will play the TADA sound when any change is made to the sheet :

    Code:
    Option Explicit
    
    
    Private Declare Function PlaySoundA& Lib "winmm.dll" (ByVal lpszName$, ByVal hModule&, ByVal dwFlags&)
    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        'If Not Intersect([L3:L24], Target) Is Nothing Then         '<--- For specific cell/s uncomment and specify the cell/s range
            PlaySoundA "C:\windows\media\tada.wav", 0&, &H20001
        'End If
    End Sub

    This is a basic email macro :

    Code:
    Option Explicit
    
    Sub EmailSnd()
        With CreateObject("outlook.application").CreateItem(0)
            .to = "email address of recipient"
            .Subject = "test"
            .CC = "cc"
            .BCC = "bcc"
            .Body = "Hello ya all"
            .Send
        End With
    End Sub

    Thank You Logit for your effort,

    anyway the main purpose is: to generate these alerts when the cluster of values in the row apper - then trigger alert (check the image in original post please); The table is growing with time (column) so the table goes wider during updates. What do You think please?

  4. #4
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    286
    Articles
    0
    Excel Version
    2007
    .
    'If Not Intersect([L3:L24], Target) Is Nothing Then '<--- For specific cell/s uncomment and specify the cell/s range

  5. #5
    Neophyte vilem.iv's Avatar
    Join Date
    Dec 2020
    Posts
    3
    Articles
    0
    Excel Version
    2019
    Oh, sorry, I am trying to place the code, but it gives an error, because I have there followign code already, where shoudl i place it please?Click image for larger version. 

Name:	section.png 
Views:	7 
Size:	13.3 KB 
ID:	10258

  6. #6
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    286
    Articles
    0
    Excel Version
    2007
    Refer back to my first post #2 :

    "The following, paste into the Sheet Module, will play the TADA sound when any change is made to the sheet :"


    Include a 'call' to the email macro in the above.

Posting Permissions

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