Adding Sound to Button

[alanj]

Hello,


I have a wav file that I would like to sound whenever a user presses a button in a spreadsheet. It is a boring workbook for my colleagues who work in the plant here. I thought it would be funny if they had a button that would make a donkey noise or a car crash when they pressed a button. I have no VBA experience, but I am curious.

 

[dan_l]

So a .wav file?


http://www.exceltip.com/st/Playing_WAV-files_using_VBA_in_Microsoft_Excel/460.html


that looks really, really simple.

 

[dan_l]

http://www.exceltip.com/st/Playing_WAV-files_using_VBA_in_Microsoft_Excel/460.html


Is that it? It looks really easy. I'll give it a shot later.

 

[dan_l]

http://www.exceltip.com/st/Playing_WAV-files_using_VBA_in_Microsoft_Excel/460.html


It looks pretty easy. Let me know if you need help.

 

[Hui]

Alan

Goto VBA, press Alt F11

On the left Right click on your workbook and Insert Module


In the right hand pane add the following code


For 32 bit computers use:

[pre]

Declare Function PlaySound Lib "winmm.dll" Alias "sndPlaySoundA" (ByVal wavFile As String, ByVal lNum As Long) As Long

Sub Play_Sound()
Call PlaySound("C:WindowsMediachimes.wav", 0) 'Change the directory and file name to suit
End Sub

If you have a 64 bit PC use this instead

Declare PtrSafe Function PlaySound Lib "winmm.dll" Alias "sndPlaySoundA" (ByVal wavFile As String, ByVal lNum As Long) As Long

Sub Play_Sound()
Call PlaySound("C:WindowsMediachimes.wav", 0)  'Change the directory and file name to suit
End Sub

[/pre]
Add a button or shape to your worksheet and assign a macro to it

Right Click on the Shape or Button and Assign Macro click on the Play_Sound macro


You will have to save the file as a Macro Enabled workbook

 

[alanj]

Hello,


I get the error:


Compile Error:


Only comments may appear after End Sub, End Function, or End Property


Any thoughts?

Thanks,

Alan

 

[Hui]

Which code did you copy ?

Where did you paste it ?

 

[Luke M]

alan,

The Declare Function needs to be at the top of the module, before any other subs.

 

[alanj]

I did it on a blank module and it works! Beautiful! Now I can add crazy sounds to the macros. Fun!

 

[CaptainKen]

I've tried this in both Access and Excel 2010 64-bit in blank modules and get the error:


Compile error:

Constants, fixed-length strings, arrays, user-defined types and Declare statements not allowed as Public members of object modules.

 

[CaptainKen]

Got it to work by prefixing the Declare statement with the word Private.