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!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,091
Have you tried this?

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

 

ProvPC

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

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

Board Regular
Joined
Nov 2, 2014
Messages
62

ADVERTISEMENT

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

Board Regular
Joined
Nov 2, 2014
Messages
62
Perhaps I'm calling myfunc() incorrectly? Can you tell me what code to attach to the button itself?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,091

ADVERTISEMENT

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:

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

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

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

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

Well-known Member
Joined
Oct 27, 2005
Messages
20,511
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

Forum statistics

Threads
1,136,202
Messages
5,674,387
Members
419,505
Latest member
Mpatsonjoka

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