Cannot get sound alert based on VBA and logical test to work.

[spire38]

I'm trying to set up VBA and logical test so that if A1 is greater than B6, a sound clip will play, alerting me even from my sleep.

Here are the codes.

VBA:

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


Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
Function SoundMe() As String
Call PlaySound("c:\soviet.wav", _
0, SND_ASYNC Or SND_FILENAME)
SoundMe = ""
End Function

Logical question in the cell:

=IF(A1>B6, SoundMe(),"")

Now, the problem is that the sound plays regardless of the logic function. It plays whether A1 is greater or less than B6.

Any help would be much appreciated. Also, I could need some help on how to stop the sound once it starts playing.

 

[iggy_]

That function seems to be working as expected for me if I create it and then use in an open workbook. Only makes the sounds when I update the values of A1 or B6. How are you using it?

 

[spire38]

That function seems to be working as expected for me if I create it and then use in an open workbook. Only makes the sounds when I update the values of A1 or B6. How are you using it?

I'm using auto-refresh with buttons.

Here's the VBA code for these functions:

Sub DataRefresh()
'
' DataRefresh Macro
'


'
ActiveWorkbook.RefreshAll
End Sub

Dim AutoRun


Sub StartAutoRefresh()
Call ScheduleAutoRefresh
End Sub


Sub ScheduleAutoRefresh()
AutoRun = Now + TimeValue("00:01:00")
Application.OnTime AutoRun, "AutoRefresh"
End Sub


Sub AutoRefresh()
ActiveWorkbook.RefreshAll
Call ScheduleAutoRefresh
End Sub


Sub StopAutoRefresh()
Application.OnTime AutoRun, "AutoRefresh", , False
End Sub

I use these commands to continuously update values from outside sources of data.

However, regardless of this, the sound plays every time. It doesn't matter if the updated value wields a false in the logical question, e.g. 15>16. Or whether I just change the value itself by hand. The sound always plays regardless of how the logical question is set.