"Sound Me" alert plays when time updates and/or whenever anything is changed on Sheet

[Marksma]

Excel 2010 and Windows 7

I have muddled through and found a VBA code that plays "tada" sound whenever the word ALERT shows up in a cell in Column E. The ALERT is formulated to show up in Column E when

1. The time in column B is within one hour of the current time (using VBA to update cell Z1 =Now() every minute)
2. The date matches the current date in Column AA (using the =today()
3. The words No Activation are in Column K

=IF(AND(B5-$Z$1<1/24,K5="No Activation",A5=$AA$1),"ALERT","") is the formula in column E

Then in column F I have this formula
=IF(E5="ALERT",SoundMe(),"")

The Sound Me VBA code that I copied and pasted into the VBA editor Module 2 is (Module 1 has the auto update the time code)

Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long

Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000

Function SoundMe() As String
Call PlaySound("c:\windows\media\tada.wav", _
0, SND_ASYNC Or SND_FILENAME)
SoundMe = ""
End Function

I would like the tada to sound only once for each time Column E changes to ALERT. Right now it seems to play tada everytime I edit any cell in other parts of the work sheet. It also seems to play every minute when the time updates???

If you can't tell. I don't really know what I'm doing. I'm a "google and try it" kind of excel user...learning as I go but not really understanding what I'm doing.

 

[Marksma]

Geez, sorry for all the posts...I don't see a way to edit my last one(s)....I think I got it to work!! I took out the Sheet 1 and Module 1 parts that reformatted Z1 and pasted yours and it seems to be working! Yay! I'm super excited! Thank you, Thank you, Thank you!

 

[sericom]

Good work