Playing sound notes using VBA in Microsoft Excel

In Excel 5 and 95 it is possible to attach notes to a cell using a sound file.
This soundnote kan be played back by opening the dialog for editing of cell notes.
The macro below can also play the sound note attached to a cell for you:

Sub PlaySoundNotesInExcel95(CellAddress As String)
' for Excel 5 and 95 only
    If Not Application.CanPlaySounds Then Exit Sub
    On Error Resume Next ' in case there is no soundnote
    Range(CellAddress).SoundNote.Play
    On Error GoTo 0
End Sub

Excel 97 or later does not longer support the use of soundnotes.
With the macros below it is possible to create a workaround to achieve the same effect:

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

Sub PlayWavFile(WavFileName As String, Wait As Boolean)
    If Dir(WavFileName) = "" Then Exit Sub ' no file to play
    If Wait Then ' play sound before running any more code
        sndPlaySound WavFileName, 0
    Else ' play sound while code is running
        sndPlaySound WavFileName, 1
    End If
End Sub

Sub PlaySoundNotesInExcel97(CellAddress As String)
' workaround for playing sound notes in Excel 97 or later
Dim SoundFileName As String
    SoundFileName = ""
    On Error Resume Next ' an error occurs if the cell doesn't have a note
    SoundFileName = Range(CellAddress).Comment.Text
    On Error GoTo 0
    If SoundFileName = "" Then Exit Sub ' no cell note
    If InStr(1, SoundFileName, Chr(10)) > 0 Then ' the note contains a line-break
        ' use the first line as the filename
        SoundFileName = Left(SoundFileName, InStr(1, SoundFileName, Chr(10)) - 1)
    End If
    PlayFileWav SoundFileName, False
End Sub

How to create a soundnote:
Insert a cell comment by rightclicking in a cell and select Insert Comment....
Fill in the complete filename and path to the soundfile to be played in the first sentence in the cell comment,
e.g. C:\Foldername\Soundfilename.wav.
If you want to add a written message in addition to the sound filename, press the ENTER-key after the filename
to create a new sentence in the comment. Add the text you want to the new sentence.
The macro PlaySoundNotesInExcel97 can be activated by the eventmacro Worksheet_SelectionChange(),
this will make the soundnote play every time the user activates the cell with the soundnote.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.