Code for making Excel beep

[ADE2]

Hi

I'm not sure how to alter the code below to achieve what i want.


I have the following code that i run at the end of a macro,which give
an audible beep if conditions are met.




If Range("B2").Value > Range("C5").Value And Range("C5") > "" Then
xcount = 1
For xcount = 1 To 5
Beep
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Next

End If


End Sub

Below is part of a formula that outlines the new conditions that nee
to be met.

I need to replace the first line of the code below so that it contain
the condtions set out in the formula,which will lead to two possibl
occassions when a beep will be emitted.

If Range("B2").Value > Range("C5").Value And Range("C5") > "" Then

I would like to alter the code above so that when either the buy o
sell conditions are met a beep is emitted.


=IF(AND(B2>C5,B3<C7),"BUY",IF(AND(B2<C5,B3>C7),"SELL",


=IF(AND(B2>C5,B3<C7) a beep is emitted

=IF(AND(B2<C5,B3>C7) a beep is emitted



Thanks for all your help


Ad

 

[Dana DeLouis]

Perhaps one of many options:

Sub Demo()
Dim j 'Counter

If [B2] > [C5] And [B3] < [C7] Or _
[B2] < [C5] And [B3] > [C7] Then
For j = 1 To 5
Beep
Application.Wait Now + TimeSerial(0, 0, 1)
Next j
End If
End Sub



If this is an unattended operation, and Buy/Sell is more critical to hear,
you may want to play a regular sound file instead of a "Beep." Excel XP
always talks back to me, especially if I am in the other room, as in this
small example:

Sub Demo()
Dim Say As Speech
Set Say = Application.Speech

If [B2] > [C5] And [B3] < [C7] Then
Say.Speak Application.UserName & ", I recommend we Buy!"
ElseIf [B2] < [C5] And [B3] > [C7] Then
Say.Speak "I recommend we sell, now!"
End If
End Sub

HTH
Dana DeLouis

 

[ADE2]

Thanks for the help Dana,that has done the trick.

Ade