"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.

 

[sericom]

Instead of a cell formula in column F, you could do the check of column E in your module 1.
You'd then store a value in column F that lets you to know the sound has been played.

eg
for each cell in column e
if cell = alert then
if cell.offset(,1) <> Yes then
call SoundMe
cell.offset(,1) = Yes
end if
else
cell.offset(,1) = No
end if
next

 

[Marksma]

Instead of a cell formula in column F, you could do the check of column E in your module 1.
You'd then store a value in column F that lets you to know the sound has been played.

eg
for each cell in column e
if cell = alert then
if cell.offset(,1) <> Yes then
call SoundMe
cell.offset(,1) = Yes
end if
else
cell.offset(,1) = No
end if
next

Is that the code I would paste in to Module 1? I'm not a code person and don't really understand what it's saying. I have just had good luck with copy/paste from forums etc. I can usually find and change a cell number or two but that's about it. It didn't like it when I pasted what you wrote above.

 

[sericom]

Nah, code won't work as is.
What rows are your formulas in for column E? and does it vary?
Post your module 1 also.

 

[Marksma]

What rows are your formulas in for column E? and does it vary?

Post your module 1 also.

Yes, it will vary. And I changed it to Column A instead of Column E...sorry.
Column A is where the word ALERT will show up and now contains the formula =IF(AND(D5-$Z$1<1/24,K5="No Activation",C5=$AA$1),"ALERT","")

So D5 is the time of scheduled activation, Z1 contains the continually updating time, K5 is the box where it must say "No Activation" to give me the ALERT, C5 is the date of activation and AA1 is the current date. I had to match the date because sometimes the activation time will cross over midnight to the next day.

The sound formula is currently in Column B and looks like this =IF(A5="ALERT",SoundMe(),"")
This gets me a sound (tada.wav) when ALERT pops into column A but it also gives the sound every minute when the time updates in Z1 and whenever I change a cell etc.

Module 1

Sub Recalc()


With Sheet1.Range("Z1")


.Value = Format(Time, "hh:mm:ss")


End With


Call SetTime


End Sub


Sub SetTime()


SchedRecalc = Now + TimeValue("00:01")


Application.OnTime SchedRecalc, "Recalc"


End Sub




Module 2

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


Sheet 1

Sub Recalc()


With Sheet1.Range("A1")


.Value = Format(Time, "hh:mm:ss")


End With


Call SetTime


End Sub


Sub SetTime()


SchedRecalc = Now + TimeValue("00:01")


Application.OnTime SchedRecalc, "Recalc"


End Sub


Sub Disable()


On Error Resume Next


Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False


End Sub


Hopefully that's clearer than mud. Thanks. I don't know if I can post a screen shot but it didn't work to paste it in here and I don't see an attachment button.




Thanks again...

 

[sericom]

Try this as Module 1

'Module 1
Sub Recalc()
    With Sheet1
        .Range("Z1").Value = Format(Time, "hh:mm:ss")
        Call SetTime
        For Each c In .Range("A5:A" & .Range("A" & Rows.Count).End(xlUp).Row)
            If c.Value = "ALERT" Then
                If c.Offset(, 1) <> "Yes" Then
                    Call SoundMe
                    c.Offset(, 1) = "Yes"
                End If
            Else
                c.Offset(, 1) = "No"
            End If
        Next
    End With
End Sub

It assumes ALERT can appear from A5 to the last used row in column in A.
Column B will be set to either Yes or No so the code will know if the sound has played or not.

 

[Marksma]

Try this as Module 1

'Module 1
Sub Recalc()
    With Sheet1
        .Range("Z1").Value = Format(Time, "hh:mm:ss")
        Call SetTime
        For Each c In .Range("A5:A" & .Range("A" & Rows.Count).End(xlUp).Row)
            If c.Value = "ALERT" Then
                If c.Offset(, 1) <> "Yes" Then
                    Call SoundMe
                    c.Offset(, 1) = "Yes"
                End If
            Else
                c.Offset(, 1) = "No"
            End If
        Next
    End With
End Sub

It assumes ALERT can appear from A5 to the last used row in column in A.
Column B will be set to either Yes or No so the code will know if the sound has played or not.

Ohh! Thank you! I tried it and it gives met his error "Compile error: Ambiguous name detected: Recalc

??

 

[sericom]

You also have a Recalc sub in Sheet1.

 

[Marksma]

You also have a Recalc sub in Sheet1.

I know this is likely simple as heck to you but I am at a preschool level here. I tried to just erase what was in Sheet 1 (it was there because whomever posted the 'google' answer I used said to put it both in Sheet 1 and Module 1).

Anyway, after I emptied my Sheet 1 I repasted yours in Module 1 and got the same error.

 

[Marksma]

Was I supposed to replace everything in Module 1 with the code you wrote or just add it?