Embed .WAV file into existing Macro

VinceF

Board Regular
Joined
Sep 22, 2007
Messages
220
Office Version
  1. 2016
Platform
  1. Windows
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

VBA Code:
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
 
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
 
Upvote 0
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.
 
Upvote 0
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

VBA Code:
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
 
Upvote 0
Below is the full code in the sheet module, where the ActiveX CommandButton1 is inserted.
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 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
 
Upvote 0
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...
 
Upvote 0
The first 4 lines:
VBA Code:
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
VBA Code:
Private Sub CommandButton1_Click()
to first
VBA Code:
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.
 
Upvote 0
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.


VBA Code:
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
 
Upvote 0
In a standard module, such as Module1, the code should look like this:
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,924
Members
453,767
Latest member
922aloose

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
Back
Top