Worksheet Event

ADE · May 25th 2004

Hi

I believe it is possible with Excel 2002 to get it to give audible alerts when an event takes place.

I use Excel for Currency trading,if cell A1 was greater than the value in cell A2,i would like to generate a sound alert and also to send an email alert.

I think this can be done with the worksheet event change facility,any help with the code would be greatly appreciated

Are either of these possible

Many thanks

ADE

yjoshi · May 27th 2004

Following code works with MS Outlook.

Hope this helps ... Interesting requirement BTW ...

Also if A1 and A2 are calculated fields, please put proper range you want to compare, or if you want to alert / send mail every time there is a change, you may choose to remove the criteria.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim olApp As Outlook.Application
Dim olMail As MailItem
    
    If Union(Target, Range("A1:A2")).Address = Range("A1:A2").Address Then
        If Range("A1").Value > Range("A2").Value And IsNumeric(Range("A1").Value) _
            And IsNumeric(Range("A2").Value) Then
            If MsgBox("Value in cell A1 is Greater than Value in Cell A2" & vbNewLine & _
                      "Do you want to send Mail?", vbYesNo) = vbYes Then
                Application.ScreenUpdating = False
                Set olApp = New Outlook.Application
                    Set olMail = olApp.CreateItem(olMailItem)
                    With olMail
                        .To = "[email protected]"
                        .Subject = "Value in Cell A1 is Greater than Value in Cell A2"
                        .Body = "Hello" & vbNewLine & vbNewLine & _
                                "Value in Cell A1 = " & Range("A1").Value & _
                                " is greater than value in cell A2 = " & Range("A2").Value
                        .Send
                    End With
                    Set olMail = Nothing
                Set olApp = Nothing
                Application.ScreenUpdating = True
            End If
        End If
    End If
End Sub

Display More

Pesky Weasel · May 27th 2004

Yogendras excellent code will take care of the email for you.

This snippet will make the sound:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A1").Value > Range("A2").Value And Range("A2") > "" Then
Beep
End If
End Sub

Regards
Weasel

lasw10 · May 27th 2004

I would not recommend using the OL object model so as to avoid problems of binding over multiple versions if used on various machines by various users, you would *think* most IT depts would ensure everyone runs on the same version but this is not always the case.

An alternative example is given below - the same Q I think ADE but in the Email Forum.

Code

Set aOutlook = GetObject(, "Outlook.Application")
        Set aEmail = aOutlook.CreateItem(0)
        'set Importance
        aEmail.Importance = 2
        'Set Subject
        aEmail.Subject = "C1 less than C2 Alert..."
        'Set Body for mail
        aEmail.Body = Cells(1, 2) & vbLf & vbLf & Cells(4, 8)
        'Set attachment if required
        'aEmail.ATTACHMENTS.Add ActiveWorkbook.FullName
        'Set Recipient
        aEmail.Recipients.Add Cells(1, 1)
        'Send Mail
        aEmail.Send

Display More

But I think you were using OE which doesn't support VBA anyway so you can't actually automate mail... you could maybe use an SMTP mailer instead.

Anyway - maybe this will come in handy for other users if not yourself.

yjoshi · May 27th 2004

Thanks for your input on this ....

Also thanks for your insight into the OL object issues

ADE · May 28th 2004

Firstly i'd like to thank everybody for their time and effort so far.

I am still working on audible alerts in Excel,the code supplied by
Pesky Weasel works,i just need to add a few extra features.

This snippet will make the sound:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A1").Value > Range("A2").Value And Range("A2") > "" Then
Beep
End If
End Sub

Once the Selection Change is triggered,i would like the beep to be REPETATIVE,perhaps beeping once a second and then after ten seconds to shut itself off and RESET itself.The code above makes only one initial beep once the event is triggered, which can be easy to miss,and then every time the mouse pointer is placed in another cell and it doesn't shut itself off.

The code for this sort of thing is way beyond my abilities,can anybody help please.

Thanks

Ade

Anonymous · May 28th 2004

Try the following (modify as desired).

In your sheet code put

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A1").Value > Range("A2").Value And Range("A2") > "" And Not ChangeDone Then
    DoBeeps
End If
End Sub

in a module put (note: the public statement needs to be at the top of the module before any subs)

Code

Public ChangeDone As Boolean, BeepCount As Integer


Sub DoBeeps()
    If BeepCount > 10 Then Exit Sub
    Beep
    BeepCount = BeepCount + 1
    If BeepCount > 10 Then Exit Sub
    Application.OnTime Now + TimeValue("00:00:01"), "DoBeeps"
End Sub

Worksheet Event

Participate now!

Share