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.
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.
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