Playing A Sound Based On A Cell’s Value

Excel Play A Sound Based On Cell’s Value              

             The created macro plays a sound according to value of changed cell in Column C.

New value can be entered to cells in Column C with combobox. In this macro, we have used 3 different sound from Windows sound library. (chimes.wav ,chord.wav ,tada.wav)

Worksheet_Change procedure of Sheet1 :
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim mh As Range, by As Range
 Application.EnableEvents = False
    Set mh = Range("C:C")
    For Each by In Range(Target.Address)
        If Not Intersect(by, mh) Is Nothing Then
            Select Case by.Value
        Case "Completed"
            PlayTheSound "chimes.wav"
        Case "Not Completed"
            PlayTheSound "chord.wav"
        Case "Pending"
            PlayTheSound "tada.wav"
    End Select
        End If
    Next by
    Set mh = Nothing
    Application.EnableEvents = True
End Sub

Our procedure that we used to play sound :
Sub PlayTheSound(ByVal WhatSound As String)
    If Dir(WhatSound, vbNormal) = "" Then
        ' WhatSound is not a file. Get the file named by
        ' WhatSound from the Windows\Media directory.
        WhatSound = Environ("SystemRoot") & "\Media\" & WhatSound
        If InStr(1, WhatSound, ".") = 0 Then
            ' if WhatSound does not have a .wav extension,
            ' add one.
            WhatSound = WhatSound & ".wav"
        End If
        If Dir(WhatSound, vbNormal) = vbNullString Then
            ' Can't find the file. Do a simple Beep.
            Exit Sub
        End If
        ' WhatSound is a file. Use it.
    End If
    ' Finally, play the sound.
    #If Win64 Then
sndPlaySound WhatSound, 0&
sndPlaySound32 WhatSound, 0&
#End If
  End Sub

No comments:

Post a Comment