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!

 

[ProvPC]

Thanks for your reply!

I've verified the path of the file, and the actual file path does have a colon. The path I displayed in the post above isn't literal, as the username in the path is more than I cared to expose on an open forum, but I'm convinced it's accurate (there wasn't really much doubt, as I got it by using the "copy as path" option in File Explorer, and pasting directly into the code). I'm sorry I neglected to put the colon in the post, but it's definitely there in the code.

And yes, there is a sound card. and the sound is not muted and the speakers are on. I can click on the file itself and play it just fine. I just can't seem to get Excel to play it at the click of a button.

 

[ProvPC]

And just to verify, I opened the code, copied the path found there and pasted it into File Explorer and pressed enter. The file played immediately. The path is correct.

 

[Eric W]

If you used an Active-X control, go to the Developer tab, go into Design Mode, then right click your Active-X button and select View Code. Put the code there, it will be on the sheet module for the sheet where your button is. Again try putting a break point on the first line with F9 to make sure that the macro is triggered when you press the button. Single step through the macro with F8 to see what happens.

 

[ProvPC]

I will give it a try - but that is what I did originally when it didn't seem to work (I changed it because when I asked about calling the myfunc routine, you mentioned it as a macro, so I swapped out the button). At some point, if you're up for it, I'd like to request that you create a blank spreadsheet, and place a button on it that calls a wav file (maybe one in C:\Windows\Media? Tada.wav, or the like?) I'd like to click the button and see whether I get sound or not.

Will report back momentarily!

 

[ProvPC]

Just to clarify, THIS entire code:

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:\Windows\Media\Tada.wav", &H0
End Sub

should go between the "Sub CommandButton1_Click()" at the top and
"End Sub" at the bottom? Like this?

Sub CommandButton1_Click()
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:\Windows Media\Tada.wav", &H0
End Sub

 

[pbornemeier]

I believe you will need to add the PtrSafe, as in

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

when running a 32-bit item on 64-bit Excel

 

[ProvPC]

I believe you will need to add the PtrSafe, as in

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

when running a 32-bit item on 64-bit Excel

I believe that's correct - thank you. Does the whole code, including the declarations, go in the CommandButton1 Click module? Like this?

Sub CommandButton1_Click()
Public Declare PtrSafe 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:\Windows Media\Tada.wav", &H0
End Sub

 

[ProvPC]

When I click on the button with the exact code above, I get a Compile error:
"Only comments may appear after End Sub, End Function, or End Property. "

This part is highlighted in blue when the compile error appears:
Public Declare PtrSafe Function sndPlaySound32 Lib "winmm.dll" Alias "sndPlaySoundA" _
(ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

 

[Eric W]

Put this:

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

at the top of a general purpose module.

Put this

Sub CommandButton1_Click()

Range("A1") = Range("A1") + 1
sndPlaySound32 "C:\Windows Media\Tada.wav", &H0

End Sub

on the sheet module.

 

[ProvPC]

Okay - so this is what I have done:

Because I've been messing about with this for a couple days now, I'm sure to have gummed things up, so I've removed all the modules in the workbook altogether. A fresh start, so to speak.

Next, in the VBA console, I've inserted a new Module:
General (Declarations) (at the top of the window)

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


Then, I created an ActiveX button (CommandButton1), right-clicked on it and chose "View Code". That module says:

CommandButton1 Cick (at the top of the window)

Private Sub CommandButton1_Click()
Range("U48") = Range("U48") + 1
sndPlaySound32 "C:\Windows Media\Tada.wav", &H0
End Sub

U48 is the cell that requires incremental updating. The tada.wav file is present and accounted for. The speakers are on and the volume is up. When I click the button, the cell increments as appropriate. But the sound does not play.

When I use F9 to stop the code after the first line, it increments the cell. Clicking the continue button moves to the next line of code (the playing of the file) and there are no error message that I can see, but there is sound, and no visible indication of anything happening at all.