Solved: Create a sound

[GTO]

OOps... I meant B4 of course.

[maninjapan]

Thanks GTO, where do I need to put this ?

[GTO]

Again, a blind stab, so definitely in a junk copy of your wb.

Option Explicit
    
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tf As Boolean, soundFile As String
Dim myVal As Double
    
    If IsError(Range("B4")) Then
        myVal = 0
    Else
        myVal = Range("B4").Value
    End If
    
    If Not Application.Intersect(Target, Range("B4")) Is Nothing _
    And myVal > Range("G3").Value _
    And Not Range("G2").Value = "ON" Then
        Application.EnableEvents = False
        Range("G2").Value = "ON"
        Application.EnableEvents = True
        soundFile = ThisWorkbook.Path & "\YesMaster.wav"
        Call sndPlaySound32(soundFile, SND_LOOP Or SND_ASYNC)
        
        If MsgBox("Kill that dang sound!", vbOKOnly Or vbQuestion) = vbOK Then
            Call sndPlaySound32(0&, 1&)
        End If
    End If
End Sub

Hope it works,

Mark

[maninjapan]

Thanks Mark, Just tried it, but anything but a number gives the same result. I also tried typing in a letter or other non numerical value, also giving the same result.

[GTO]

Thanks Mark, Just tried it, but anything but a number gives the same result. I also tried typing in a letter or other non numerical value, also giving the same result.

What result?

I forced an #N/A by entering a bad MATCH() formula, and MyVal is assigned = 0

[maninjapan]

GTO, I hadn't copied your code across properly. It works fine. Thanks a lot !

[GTO]

I should be in Vegas, I'm on a streak Glad that worked :-)

[maninjapan]

back with what seems to be a small kink in VBA that we have to date.
It triggers exactly as it should when i type in numbers manually to the cell.
However in reality the cell being watched is a sum of 4 other cells which show PL through a RTD link.
I fired it up for the first time today using the RTD data, but it wasn't executing ( even though the total PL was over the threshold).
If I clicked on the cell and pressed enter after the formula it recognizes the condition as being filled, but unless i do that the total PL freely moves through the threshhold without triggering the alert.
I'm not sure what is going on here, but as the macro stands does it not see that the number in the cell is actually changing?

[Kenneth Hobs]

If it is a formula, then you need to check it at each calculate event. The sheet's Calculate event does not have a Target as an input parameter.

[maninjapan]

sorry Kenneth, that went a bit over my head. where do I need to make the tweaks in the code GTO supplied?

[Kenneth Hobs]

You can right click the sheet's tab and View Code and paste sheet code like that below or doubleclick the sheet object in the VBE and paste sheet code.

You should rename the Change event code or delete it so that it will not activate.

Another way to add event subs to a sheet object is to be in the sheet's code section as first detailed, select the Worksheet in the first dropdown list and the event in the second dropdown list.

[VBA]Private Sub Worksheet_Calculate()
Dim tf As Boolean, soundFile As String
Dim myVal As Double

If IsError(Range("B4")) Then
myVal = 0
Else
myVal = Range("B4").Value
End If

If myVal > Range("G3").Value And Range("G2").Value <> "ON" Then
Application.EnableEvents = False
Range("G2").Value = "ON"
Application.EnableEvents = True
soundFile = ThisWorkbook.Path & "\YesMaster.wav"
Call sndPlaySound32(soundFile, SND_LOOP Or SND_ASYNC)

If MsgBox("Kill that dang sound!", vbOKOnly Or vbQuestion) = vbOK Then
Call sndPlaySound32(0&, 1&)
End If
Application.EnableEvents = True
End If
End Sub[/VBA]

[maninjapan]

Sorry kenneth, now Im completely lost. I just tried adding the code you posted to the Sheet Module and it comes up with an error . Is this supposed to work as it is?

Thanks

[Kenneth Hobs]

Saying it has an error does not help me much. Always use Option Explicit. If you don't have the Compile button on your VBE toolbar, I recommend adding it as-well-as the option to add Option Explicit for you.

To answer your question, no, it was meant to work with the module mGTO that I posted in the xlsm earlier. In it, constants and the API were defined. A compile shows error for Snd_Loop if it was not defined so that is a clue. Other errors could be that the sound file does not exist. That check can easily be added though with a Dir().

The mGTO code was:
[VBA]Public Declare Function sndPlaySound32 Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName _
As String, ByVal uFlags As Long) As Long

' flag values for uFlags parameter
Public Const SND_SYNC = &H0 ' play synchronously (default)
Public Const SND_ASYNC = &H1 ' play asynchronously

Public Const SND_NODEFAULT = &H2 ' silence not default, if sound not found

Public Const SND_MEMORY = &H4 ' lpszSoundName points to a memory file
Public Const SND_ALIAS = &H10000 ' name is a WIN.INI [sounds] entry
Public Const SND_FILENAME = &H20000 ' name is a file name
Public Const SND_RESOURCE = &H40004 ' name is a resource name or atom
Public Const SND_ALIAS_ID = &H110000 ' name is a WIN.INI [sounds] entry identifier

Public Const SND_ALIAS_START = 0 ' must be > 4096 to keep strings in same section of resource file

Public Const SND_LOOP = &H8 ' loop the sound until next sndPlaySound
Public Const SND_NOSTOP = &H10 ' don't stop any currently playing sound
Public Const SND_VALID = &H1F ' valid flags / ;Internal /

Public Const SND_NOWAIT = &H2000 ' don't wait if the driver is busy

Public Const SND_VALIDFLAGS = &H17201F ' Set of valid flag bits. Anything outside
' this range will raise an error
Public Const SND_RESERVED = &HFF000000 ' In particular these flags are reserved

Public Const SND_TYPE_MASK = &H170007
[/VBA]

[maninjapan]

Kenneth, thank you for your patience on this. I wasn't using a copy/paste of your code as I had changed some of the conditions slightly and skipped over the change from Worksheet_Change to Corksheet_Calculate.

Your code worked perfectly once I found my own error.

Thank you again.

[zman21]

So, when you guys say "embed" does that actually mean the sound file is stored within the worksheet? I'm trying to play a sound when an image is clicked. But, I don't want to have to store the sound file locally on the machine.

[Kenneth Hobs]

Welcome to the forum Zman21!

Please start a new thread unless your post is to help with the current thread. Solved threads are best left alone unless the original posters (op) needs clarification or the helper has other tips. Since you have less than 5 posts, rather than posting a link to a related thread, just say my issue is similar post 37056 but I need to.....

To answer your question, yes. See my example file.