Playing sound notes using VBA in Microsoft Excel

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/general-topics-in-vba/playing-sound-notes-using-vba-in-microsoft-excel.html
SHARE




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.

Please follow and like us:
0


Leave a Reply

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

To avoid automated spam,Please enter the value *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>