Play a sound after sheet calculation

[Craig92604]

I have a spreadsheet that looks like a slot machine. When I hold down F9 key is there a macro that can play a sound after each calulation kinda like a real slot machine? I am using conditional formating to change the colours

 

[Andrew Fergus]

Hi

Copy the following code into the Worksheet Calculate event :

Option Explicit

Private Declare Function GetAsyncKeyState Lib "user32" _
    (ByVal vKey As Long) As Integer

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

Private Const VK_F9 = &H78

Private Sub Worksheet_Calculate()

If GetAsyncKeyState(VK_F9) < 0 Then
    sndPlaySound "C:\WINDOWS\Media\tada.wav", 0
End If

End Sub

Change the file path and sound file name to the actual location and name of the sound file you want to play. If you want the code to stop while it plays the sound keep the code as is, but if you want the code to continue processing while playing the sound then change the ',0' part to ',1' (without the quotes).

HTH, Andrew

P.S. Credit to Tom Urtis in this thread for the key press routine.

 

[Craig92604]

Not sure what I am doing wrong with this code. When I run it I get the following error "

Compile error:

Only comments may appear after end Sub, End Function, or end property?
Any help would be appreciatted


Private Sub Worksheet_Calculate()
Option Explicit

Private Declare Function GetAsyncKeyState Lib "user32" _
(ByVal vKey As Long) As Integer

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

Private Const VK_F9 = &H78

Private Sub Worksheet_Calculate()

If GetAsyncKeyState(VK_F9) < 0 Then
sndPlaySound "C:\WINDOWS\Media\tada.wav", 0
End If

End Sub

 

[sam slade]

Craig,

you don't need the 1st Private Sub Worksheet_Calculate()

That should make it work.

Sam

 

[Craig92604]

Worked perfectly now! Thanks for everyones help on this!!!