Click a Button, Play a WAV File (for the Dull of Wit)

[ProvPC]

I've been struggling with this seemingly simple operation for two days now, and I just can't seem to get it. I've read and done my best to follow numerous examples given - many within this very forum - and have encountered an extraordinary variety of error messages in the process (and sometimes, no error messages at all, but also no results).

I have a 64-bit version of Excel 2016. I'm trying to create a button that will do two things:
1) Increment the value in a given cell by 1
2) Play a sound. Extra points if this part doesn't necessitate the launch of a separate program, but I'll take what I can get!

The file needs to be portable, but I am not bothered by whether it's easiest to embed the audio file, or to summon it from the same directory the spreadsheet inhabits - whatever you feel works best, I'll be delighted with! I'm just requesting some clear, precise instructions on how to get the silly thing to work. )

I wish I'd kept a list of all the error messages, but they've included things like needing to optimize and add the PtrSafe function for 64-bit, value undefined, Public declarations not allowed, cannot locate insertion point, and just plain nothing at all (least of all audio!)

Any assistance would be much appreciated!

 

[Eric W]

Have you tried this?

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

Sub MyFunc()
    Range("A1") = Range("A1") + 1
    sndPlaySound32 "C:\Temp\Tada.wav", &H0
End Sub

Set up a button to call the MyFunc procedure. This works for an external file, working with embedded files seems trickier, see here:

Play embedded wav file when workbook opens but without showing the media player

Running Excel 2013. I've done quite a bit of searching on this one this morning and from most of the info I read it doesn't look like this can be done. However, most of the info was years old so I'm hoping over the years something changed and I will be able to have this work successfully. I...

www.mrexcel.com

 

[ProvPC]

Thanks for your reply. I hope I'm putting things where they belong, but when I try your suggestion, I get an error:
Compile error:
The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PrtSafe attribute.

If I change your example to Public Declare PtrSafe Function.... and try again, I get another error:
Compile error:
Constants, fixed-length strings, arrays, user-defined types and Declare statements not allowed as Public members of object modules.

If I change Public to Private, and try again, I get
Compile error:
Expected End Sub

 

[Eric W]

Interesting. It works on my PC which I think is 64-bit. Try changing the

sndPlaySound32

to

sndPlaySound64

in 2 places and change it back to Public.

 

[ProvPC]

Okay - Here's what I have:

Public Declare PtrSafe Function sndPlaySound64 Lib "winmm.dll" Alias "sndPlaySoundA" _
(ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

Sub MyFunc()
Range("U40") = Range("U40") + 1
sndPlaySound64 "c\mypath\noise.wav", &H0
End Sub

Now, when I click on the button, I get no error message at all - but also no sound and no increment of the cell.

 

[ProvPC]

Perhaps I'm calling myfunc() incorrectly? Can you tell me what code to attach to the button itself?

 

[Eric W]

How do you create the button? Go to the Developer tab > Insert > button, and then use Assign Macro, selecting MyFunc? That should work. Put a breakpoint on the first line of code with F9, and when you click the button, it should stop. If it doesn't stop, then the macro isn't being called.

As far as the cell not incrementing, is it on another sheet? If so, try this:

Sheets("Sheet2").Range("U40")=Sheets("Sheet2").Range("U40") + 1

As far as the sound, it looks like you're missing a colon after the c:

sndPlaySound64 "c:\mypath\noise.wav", &H0

 

[ProvPC]

Thanks for your patience, and for hanging in there with me!

The cell is on the same worksheet as the button. And the file path does have a colon. The path I displayed in this post isn't literal, as the username is more than I cared to expose on an open forum, but I'm convinced it's accurate.

However... we may be on to something in the matter of the button. I created the button as an ActiveX control, rather than a Form control, so there's no "assign macro" option. Instead, I was trying to call the function through code (which clearly isn't working). I'll give it another shot and report back.

 

[ProvPC]

Hm. Well, we're back to the Compile error:
Constants, fixed-length strings, arrays....not allowed as Public members of object modules.

If I change it to Private, there are no error messages, and the cell now increments appropriately, but the sound still does not play. I've verified the path of the file (there wasn't really much doubt, as I got it by using the "copy as path" option in File Explorer, and pasting it in the code) (

I'm sorry to be a pest, but I can't figure out what I'm doing wrong. It's probably something ridiculously obvious.

 

[Michael M]

does this file actually exist at that location
AND
shouldn't it be.....with a colon
"c:\mypath\noise.wav"
Do you have
1. a sound card
2. sound turned on