Embed two sound files into one macro

1

I have a macro and would like it to play an embedded 3 second sound file when worksheet is opened.

After sound file ends, macro to start with cell selection, and every click thereafter plays a 2nd embeded sound file.

Answer
Discuss

Discussion

I'm not at my laptop so I can't be of much help right now but I'll try to take a look at it tomorrow.
don (rep: 1382) Aug 7, '17 at 4:19 pm
Please don't forget to select the answer that worked best for you! Just click the Select Answer button at the bottom of the desired answer.
don (rep: 1382) Aug 31, '17 at 7:43 am
Add to Discussion

Answers

1

Here is the code including the new additions to it. I guide you through the entire installation process for the benefit of newcomers to the project.

First create a new standard code module in your project (not a Class module and not a UserForm). By default it will be called "Module1". You can rename it if you wish. Paste the following code into it.

Option Explicit
#If VBA7 Then
    Private Declare PtrSafe Function sndPlaySound32 _
                            Lib "winmm.dll" _
                            Alias "sndPlaySoundA" ( _
                            ByVal lpszSoundName As String, _
                            ByVal uFlags As Long) As Long
#Else
    Private Declare Function sndPlaySound32 _
                    Lib "winmm.dll" _
                    Alias "sndPlaySoundA" ( _
                    ByVal lpszSoundName As String, _
                    ByVal uFlags As Long) As Long
#End If
Sub PlaySound(SoundId As Long)
    ' 08 Aug 2017
    
    ' include final backslash:
    Const PathName As String = "C:\Windows\Media\"
    Const FileFormat As String = ".wav"                 ' include leading period
    
    Dim Fun As String
    Dim FileNames() As String
    
    ' list your file names comma-separated:-
    ' (the first item assumes SoundId = 0, etc.)
    FileNames = Split("Chimes,Chord", ",")
    
    Fun = PathName & Trim(FileNames(SoundId)) & FileFormat
    sndPlaySound32 Fun, 0&
End Sub

In this code you need to change the constant "PathName" to where your sound bites are. In the variable "FileNames" you may enter the names of the files you want to use. Separate them with commas. The first name will be the sound which is played when the worksheet is activated.

Now paste the following code in thre module "ThisWorkbook".

Private Sub Workbook_Open()
    PlaySound 0
End Sub

Finally, this is the revised code to go into the code sheet of the worksheet which has all the action. Observe that the start-up sound is also played when you activate the worksheet.

Option Explicit
    Const RangeAddress As String = "C2:J5"
    
Private Sub Worksheet_Activate()
    PlaySound 0
    ShowRandomCell
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Rng As Range
    Dim CellId As Long
    Set Rng = Range(RangeAddress)
    If Not Application.Intersect(Target, Rng) Is Nothing Then
        If Target.Font.Color = vbBlack Then
            ShowRandomCell
            PlaySound 1
        End If
    End If
End Sub
Private Sub ShowRandomCell()
    Static PreviousId As Long
    Dim Rng As Range
    Dim CellId As Long
    
    Set Rng = Range(RangeAddress)
        Rng.Font.Color = vbWhite
        Do
            Randomize
            CellId = Int(Rnd * Rng.Cells.Count) + 1
        Loop While CellId = PreviousId
        
        With Rng.Cells(CellId)
            .Font.Color = vbBlack
        End With
        PreviousId = CellId
End Sub
Discuss

Discussion

Thank you mate you are awesome. This is incredible job.
Adam (rep: 4) Aug 7, '17 at 10:18 pm
I'm glad it all seems to work as I hoped it would. Please accept my answer and welcome to ask your next question, anytime.
Variatus (rep: 1423) Aug 7, '17 at 11:28 pm
I have modified the API declaration in my above code to run on both 32-bit and 64-bit systems. If you plan for your project to run on computers other than your own it will be better to take this version of the code. The difference is only in the first part of the first block of code which now has two versions of the API sndPlaySound32.
Variatus (rep: 1423) Aug 8, '17 at 4:32 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login