Specific change using Worksheet_Change

[NoobishNewb]

Hi all,

I would like to know how to make excel play a sound when a specific change occurs.

For example, I have a range of cells (F6 to F50) with formula within the cells. They are blank at first (which is normal). When the stock market moves, some of the cells (say F8 and F25) are "Triggered" based on the formula and play a sound accordingly. As the price of the stock changes, cell F8 changes from "Triggered" back to blank. However, because F25 still remains as "Triggered", excel plays a sound again.

I would like excel to play a sound when the cell changes from blank to "Triggered". But when the cell changes from "Triggered" back to blank, there should not be a sound even though there are other cells within F6 to F50 with the word "Triggered".

Please see below for what I have tried. (Btw I am a newbie in VBA).
Thanks!


Private Sub Worksheet_Change(ByVal Target As Range)


Dim Cell As Range
Dim CheckRange As Range
Dim PlaySound As Boolean


Set CheckRange = Range("F6:F50")


If Not Intersect(Target, CheckRange) Is Nothing Then


For Each Cell In CheckRange
If Cell.Value = "Triggered" Then
PlaySound = True
End If
Next


If PlaySound Then
Call PlayTrigger
End If

End If


End Sub

 

[Yongle]

I would like excel to play a sound when the cell changes from blank to "Triggered". But when the cell changes from "Triggered" back to blank, there should not be a sound

Try something like this:

Switch trigger to OFF
put this as 1st line of Worksheet_Change macro

Application.EnableEvents = False

Switch trigger to ON
then AFTER the line changing the value back to blank (last line of macro should work)

Application.EnableEvents = True

 

[NoobishNewb]

Hi Yongle,

Not sure where exactly to put the codes. There is no line to change the value back to blank. It happens when the price changes and the formula is in the cell itself.
And also by putting the Application.EnableEvents = False at the first line, there is no sound when the trigger happens.
Please kindly advise.

Thanks!

Try something like this:

Switch trigger to OFF
put this as 1st line of Worksheet_Change macro

Application.EnableEvents = False

Switch trigger to ON
then AFTER the line changing the value back to blank (last line of macro should work)

Application.EnableEvents = True

 

[Yongle]

Switch trigger to OFF after line

Next

Switch trigger to ON as last line of macro

 

[NoobishNewb]

Thanks Yongle

Switch trigger to OFF after line

Next

Switch trigger to ON as last line of macro