Embed .WAV file into existing Macro

[VinceF]

Help...Really struggling with this, I'm sure it'll be simple for the experts.

I have a Active X button that executes a macro. I'm trying to have a .wav file (click.wav) play when the button is pressed.
With my extremely limited VBA skills I've been unable to make it happen...The code below is where I'm trying to place it into.
Thanks
VinceF
Win10
Office365

Sub GenerateMultipleRandomintegers()

    

For i = 1 To 3
    Do
'       Calculate number
        n = WorksheetFunction.RandBetween(1, 18)
'       Check to see if number already used in range
        If Application.WorksheetFunction.CountIf(Range("A1:A3"), n) = 0 Then
'           If not duplicate, assign to cell and exit loop
            Range("A" & i) = n
            Exit Do
        End If
    Loop
Next i


   
    
End Sub

 

[Joe4]

I have never done this before, but there seems to be a bunch of old threads dealing with this that can be found with a simple search.
Here is one here: playing .wav file sounds from Excel

 

[VinceF]

Thank you Joe4 for your reply.
Prior to posting I searched the old post and searched the internet. Many examples came up and I tried to implement all of them but to no avail.
I also went to the link that you provided and I still am not able to get it to work. I'm doing something wrong but I haven't a clue as to what it is.
My VBA skills are extremely limited...pretty much copy and paste.
Thanks

 

[Joe4]

Please post your attempts at incorporating this into your code, and someone may be able help you debug it and see where things went wrong.

 

[VinceF]

Will do..

I inserted the .wav file into an existing code and when I run it I get "compile error, sub of Function not defined

any suggestions?

VinceF
Win10
Office365

Sub GenerateMultipleRandomintegers()

playSound "C:\Golf\Indianwood\click.wav", 0, 0


For i = 1 To 3
    Do
'       Calculate number
        n = WorksheetFunction.RandBetween(1, 18)
'       Check to see if number already used in range
        If Application.WorksheetFunction.CountIf(Range("A1:A3"), n) = 0 Then
'           If not duplicate, assign to cell and exit loop
            Range("A" & i) = n
            Exit Do
        End If
    Loop
Next i
    
End Sub

 

[Artik]

Below is the full code in the sheet module, where the ActiveX CommandButton1 is inserted.

Option Explicit

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


Private Sub CommandButton1_Click()
    Dim i As Long
    Dim n As Long

    For i = 1 To 3
        Do
            'Calculate number
            n = WorksheetFunction.RandBetween(1, 18)
            'Check to see if number already used in range
            If Application.WorksheetFunction.CountIf(Range("A1:A3"), n) = 0 Then
                'If not duplicate, assign to cell and exit loop
                Range("A" & i) = n
                Exit Do
            End If
        Loop
    Next i

    sndPlaySound "C:\Golf\Indianwood\click.wav", 0

End Sub

Artik

 

[VinceF]

Artik,
Thanks for the attempt. When I create the Active X button it places it at the end of another module. So I copied and pasted the code exactly as you wrote and I get the following error.
Ambiguous name detected
CommandButton1.click

I'm sure it's my lack of understanding what I'm doing.
Thanks...

 

[Kaper]

The first 4 lines:

Option Explicit

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

shall be at the very top of the module, and the new code of CommandButton1_Click() shall replace previous code

so select existing code from

Private Sub CommandButton1_Click()

to first

End Sub

below

And delete it.

As you already have two versions of CommandButton1_Click() in the module make sure you are deleting the old one.

Also if previously you copied Option Explicit etc. somewhere in the middle of module, please delete it to have these 4 lines only at the top of the module and not duplicated.

 

[VinceF]

Kaper...thank you for your input.

Below is the macro that I'm attempting to put this code it into.... named, GenerateMultipleRandomintegers()
I also do not have a module or macro named CommandButton1_Click(), I only created that module in an attempt to make this work and have deleted it.

When I run this code as shown below, it brings up a window showing all the macro's in this workbook and the GenerateMultipleRandomintegers() macro is highlighted. So I select RUN from there and I get the following compile error "Ambiguous name detected GenerateMultibleRandomintergers"

I very much appreciate your and everyone's attempt at resolving this issue.

Sub GenerateMultipleRandomintegers()
VBA Code:
Option Explicit

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


Private Sub GenerateMultipleRandomintegers()
    Dim i As Long
    Dim n As Long

    For i = 1 To 3
        Do
            'Calculate number
            n = WorksheetFunction.RandBetween(1, 18)
            'Check to see if number already used in range
            If Application.WorksheetFunction.CountIf(Range("A1:A3"), n) = 0 Then
                'If not duplicate, assign to cell and exit loop
                Range("A" & i) = n
                Exit Do
            End If
        Loop
    Next i

    sndPlaySound "C:\Golf\Indianwood\click.wav", 0

End Sub

 

[Artik]

In a standard module, such as Module1, the code should look like this:

Option Explicit

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


Sub GenerateMultipleRandomintegers()
    Dim i As Long
    Dim n As Long

    For i = 1 To 3
        Do
            'Calculate number
            n = WorksheetFunction.RandBetween(1, 18)
            'Check to see if number already used in range
            If Application.WorksheetFunction.CountIf(Range("A1:A3"), n) = 0 Then
                'If not duplicate, assign to cell and exit loop
                Range("A" & i) = n
                Exit Do
            End If
        Loop
    Next i

    sndPlaySound "C:\Golf\Indianwood\click.wav", 0

End Sub

Artik