Made Sound Alert More Than Once

[DaleKeel]

I found this

=IF(U70>=W70,SoundMe(),"")

and it works fine for what I have been doing. Problem is now I need for the sound to alert twice or three times and then stop (after the condition is met). Can anyone help me? Thanks.

For a different situation:
I need an alarm with a different sound than the above sound. Is that possible?

Thanks for your time.

 

[Logit]

Option Explicit

Private Declare Function sndPlaySound32 Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName _
As String, ByVal uFlags As Long) As Long

Sub Delete_Column()
    Dim PlaySound As Boolean
    Call sndPlaySound32("C:\windows\media\chord.wav", 1)
End Sub

With the above, so long as the sound is a WAV file and you have the correct path to the file ("C:\windows\media\chord.wav") ... you should be good to go.

 

[DaleKeel]

I copied your statement into a VBA module

In cell A1 50
In cell b1 40

so in cell c1 i put =IF(A1>=B1,sndPlaySoundA(),"") and it did not work what is wrong? Thanks

there is a ding.wav file in windows media

 

[Logit]

That macro is designed to be run by a COMMAND BUTTON.

 

[DaleKeel]

So your code has nothing to do with meeting a condition but just pushing a button?
If so I do not think that was what I was looking for in my description of my problem?
Thanks for you time thu

 

[Logit]

Try changing the name of the sub from Delete_Column to SoundMe and see if it runs with your formula.

 

[Jaafar Tribak]

How about this :

In a Standard Module:

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function PlaySound Lib "winmm.dll" Alias "sndPlaySoundW" (ByVal lpszSoundName As LongPtr, ByVal uFlags As Long) As Long
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    Private Declare Function PlaySound Lib "winmm.dll" Alias "sndPlaySoundW" (ByVal lpszSoundName As Long, ByVal uFlags As Long) As Long
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If


Public Function SoundMe(ByVal SoundFile As String, Optional ByVal HowManyTimes As Integer = 1, Optional ByVal IntervalDurationInSecs As Single = 0.5)

    Const SND_ASYNC = &H1
    Const SND_NODEFAULT = &H2
    Static iCounter As Integer
   
    On Error GoTo ErrHandler
   
    For iCounter = 1 To HowManyTimes
        DoEvents
        Call PlaySound(StrPtr(SoundFile), SND_ASYNC)
        If iCounter >= HowManyTimes Then GoTo ErrHandler
        Call Sleep(IntervalDurationInSecs * 1000)
    Next iCounter
   
    Exit Function
   
ErrHandler:
    iCounter = 0
    Call PlaySound(StrPtr(SoundFile), SND_NODEFAULT)
   
End Function

Then you call it in a cell formula as follows :

=IF(U70>=W70,SoundMe("C:\windows\media\chord.wav",3,0.5),"")

Where 3 is the number of times the sound will play and 0.5 is the interval in seconds between each playing .. these two extra arguments are optional and if they are not set they will default to 1 time play and 0.5 Secs respectively.

 

[DaleKeel]

Nope thanks anyway

 

[DaleKeel]

Works great in a new workbook. The workbook where I will use it has the =IF(U70>=W70,SoundMe(),"") with default windows sound( not a wav)
Do I have to change yours so they do not conflict?
Is changing yours to SoundU going to make a difference in it working?

Thanks very much this is a great notification alert. Now I can tell the difference in what is changing with my numbers and immediately tells me where to look on the sheet.

 

[Jaafar Tribak]

If you are happy to use the function I posted then you will need to get rid of the SoundMe function you already have somewhere in your vbproject.
After you have done that, you will need to adjust every cell formula that uses the SoundMe function as you will now need to pass the wav file name & path in the first required argument.

You can also optionally set the two other arguments to detremine how many times the sound will play and at which intervals.