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

ProvPC

Board Regular
Joined
Nov 2, 2014
Messages
62
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. :eek:)

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

Board Regular
Joined
Nov 2, 2014
Messages
62
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.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

ProvPC

Board Regular
Joined
Nov 2, 2014
Messages
62
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,091
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

Board Regular
Joined
Nov 2, 2014
Messages
62
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

Board Regular
Joined
Nov 2, 2014
Messages
62

ADVERTISEMENT

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

Well-known Member
Joined
May 24, 2005
Messages
3,890
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

Board Regular
Joined
Nov 2, 2014
Messages
62

ADVERTISEMENT

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

Board Regular
Joined
Nov 2, 2014
Messages
62
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,091
Put this:

VBA Code:
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

VBA Code:
Sub CommandButton1_Click()

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

End Sub
on the sheet module.
 

ProvPC

Board Regular
Joined
Nov 2, 2014
Messages
62
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.
 

Forum statistics

Threads
1,136,200
Messages
5,674,379
Members
419,503
Latest member
tismail

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top