Play .wav File When Cell Contents Change

[VinceF]

Greetings Experts,
I've searched this site along with the web and have tried probably 10 different ways to get the following to work...all to no avail.

I have a sheet named "MAIN"
Cells B4:G4 and cells O2 & O3 all have Drop Down Lists, via data validations.
When a selection is made from the DDL's I'm trying to get a .wav file to play. C:\Golf\Sounds\Click.wav
From Visual Basic I select the "MAIN" sheet, insert Module, paste the suggested codes and to this point none of the suggested codes have worked.
This will be added to the existing code as show below.

Appreciate any help
VinceF
Win10, 64 bit
Excel365
P.S. VBA skills limited to "Copy n' Paste"

Private Sub RESET_Click()

Dim warning
warning = MsgBox(Range("D1").Value & "                                             << CAUTION  >>THIS WILL RESET THE ENTIRE SHEET                  Select OK to Continue or Select CANCEL to Continue Without Resetting", vbOKCancel, "1. Save Round  2. Save Results 3. Adjust Qta NEW or OLD Course  ")
If warning = vbCancel Then Exit Sub


On Error Resume Next

Sheets("main").Range("B11:AM50").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("C5:C8").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("D3:G3").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("O11:O50").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("K11:K50").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("M11:M50").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("A1:A3").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("I8:M8").SpecialCells(xlCellTypeConstants).ClearContents

Sheets("main").Range("B2").Value = "SELECT COURSE"
Sheets("main").Range("B4").Value = "OLD    NEW"
Sheets("main").Range("C4").Value = "STR ADJ"
Sheets("main").Range("D4").Value = "SELECT AMOUNT"
Sheets("main").Range("E4").Value = "SELECT AMOUNT"
Sheets("main").Range("F4").Value = "SELECT AMOUNT"
Sheets("main").Range("G4").Value = "SELECT AMOUNT"
Sheets("main").Range("C2").Value = "SELECT STR ADJ"
Sheets("main").Range("C3").Value = "HALF    FULL"
Sheets("main").Range("C6").Value = "QUOTA     POT"
Sheets("main").Range("C8").Value = "PAY PER POINT"

Sheets("main").Range("D2").Value = "QUOTA AMOUNT"
Sheets("main").Range("E2").Value = "RANDOM 3 HOLE"
Sheets("main").Range("E3").Value = "SELECT AMOUNT"
Sheets("main").Range("F2").Value = "SKINS AMOUNT"
Sheets("main").Range("F3").Value = "SELECT AMOUNT"
Sheets("main").Range("G2").Value = "PAR 3  GAME"
Sheets("main").Range("G3").Value = "SELECT AMOUNT"
Sheets("main").Range("H2").Value = "TOTAL POT                     MONEY COLLECTED"

Sheets("main").Range("B11").Value = "SKINS"
Sheets("main").Range("C11").Value = "QUOTA"
Sheets("main").Range("D11").Value = "PAR 3"
Sheets("main").Range("E11").Value = "RAND 3"
Sheets("main").Range("F11").Value = "TOTAL"
Sheets("main").Range("G11").Value = "ALT TEE"
Sheets("main").Range("H11").Value = "PLAYER"
Sheets("main").Range("I11").Value = "INDEX"
Sheets("main").Range("J11").Value = "HDCP"
Sheets("main").Range("J6").Value = "RANDOM 3 HOLE                             GAME WINNER(s)"
Sheets("main").Range("K11").Value = "HDCP"
Sheets("main").Range("M11").Value = "QUOTA"

Sheets("main").Range("O11").Value = "PAR 3"
Sheets("main").Range("P11").Value = "RAND 3"
Sheets("main").Range("Q11").Value = "F R O N T    N I N E "
Sheets("main").Range("Z11").Value = "IN"
Sheets("main").Range("AA11").Value = "B A C K    N I N E"
Sheets("main").Range("AJ11").Value = "OUT"
Sheets("main").Range("AK11").Value = "TOT"
Sheets("main").Range("AL11").Value = "QTA PTS"
Sheets("main").Range("AM11").Value = "+/- QTA"
Sheets("main").Range("AN11").Value = "SKINS"




Sheets("main").Range("I2").Value = "TOTAL AMOUNT   PAID OUT"
Sheets("main").Range("K2").Value = "INCLUDE    STAFF"
Sheets("main").Range("K3").Value = "SELECT YES OR NO"
Sheets("main").Range("M2").Value = "AMOUNT  PAID         TO  STAFF"
Sheets("main").Range("O2").Value = "SELECT GAME"
Sheets("main").Range("O3").Value = "SELECT TEE"
Sheets("main").Range("O7").Value = "HDCP"
Sheets("main").Range("R2").Value = "ROUND #"
Sheets("main").Range("T3").Value = "SAVE      ROUND"

Sheets("main").Range("R3").Value = "SELECT RANDOM NUMBERS"
Sheets("main").Range("T3").Value = "1. SAVE THIS ROUND"
Sheets("main").Range("V3").Value = "RECORD GAME RESULTS"
Sheets("main").Range("X3").Value = "ADJUST QTA OLD COURSE"
Sheets("main").Range("Z3").Value = "ADJUST QTA NEW COURSE"



Sheets("main").Range("AB2").Value = "N   A   V   I   G   A   T   I   O   N"
Sheets("main").Range("AB3").Value = "ADD     NEW      PLAYER"
Sheets("main").Range("AD3").Value = "YTD         WON      LOST"
Sheets("main").Range("AF3").Value = "VINNY               CUP      GAME"
Sheets("main").Range("AH3").Value = "INDIVIDUAL      SIDE          BETS"
Sheets("main").Range("AJ3").Value = "INDVIDUAL     SCORING  HISTORY"
Sheets("main").Range("AL3").Value = "ROUND #"

Sheets("main").Range("O6").Value = "HOLE"
Sheets("main").Range("O7").Value = "YARDS"
Sheets("main").Range("O8").Value = "HDCP"
Sheets("main").Range("O9").Value = "PAR"



Sheets("main").Range("Z6").Value = "IN"
Sheets("main").Range("AJ6").Value = "OUT"
Sheets("main").Range("AK6").Value = "TOTAL"
Sheets("main").Range("Q6").Value = "1"
Sheets("main").Range("R6").Value = "2"
Sheets("main").Range("S6").Value = "3"
Sheets("main").Range("T6").Value = "4"
Sheets("main").Range("U6").Value = "5"
Sheets("main").Range("V6").Value = "6"
Sheets("main").Range("W6").Value = "7"
Sheets("main").Range("X6").Value = "8"
Sheets("main").Range("Y6").Value = "9"
Sheets("main").Range("AA6").Value = "10"
Sheets("main").Range("AB6").Value = "11"
Sheets("main").Range("AC6").Value = "12"
Sheets("main").Range("AD6").Value = "13"
Sheets("main").Range("AE6").Value = "14"
Sheets("main").Range("AF6").Value = "15"
Sheets("main").Range("AG6").Value = "16"
Sheets("main").Range("AH6").Value = "17"
Sheets("main").Range("AI6").Value = "18"

Sheets("main").Range("F73").Value = ""
Sheets("main").Range("F75").Value = ""
Sheets("main").Range("F77").Value = ""

Sheets("main").Range("C1").Value = "INDIANWOOD"
Sheets("main").Range("D1").Value = "                      For Questions Contact VinE ***.***.****"

MsgBox "THE FORM HAS BEEN RESET."

Application.DisplayAlerts = False
   ActiveWorkbook.SaveAs ("C:\Golf\Indianwood\" & ThisWorkbook.Sheets("Main").Range("C1").Value & ".xlsm")
   Range("AL4") = Range("AL4") + 1
Application.DisplayAlerts = True
Application.Quit

On Error Resume Next

     
End Sub

 

[Logit]

Paste the following into the SHEET LEVEL MODULE. In other words, if the drop down are located in Sheet 1, paste the macro code in the sheet level module for sheet 1.

Option Explicit

Private Declare Function sndPlaySound32 Lib "winmm.dll" Alias "sndPlaySoundA" (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wavFilePath As String
    Dim rng1 As Range
    Dim rng2 As Range

    ' Define the ranges to check
    Set rng1 = Range("B2:G4")
    Set rng2 = Range("O2:O3")

    ' Check if the changed cell is within the specified ranges
    If Not Intersect(Target, rng1) Is Nothing Or Not Intersect(Target, rng2) Is Nothing Then
        ' Set the path to the .wav file
        wavFilePath = ThisWorkbook.Path & "\cuckoo_clock2_x.wav"     '<<--------------------  edit wave file name here

        
        ' Play the .wav file
        Call sndPlaySound32(wavFilePath, 0)
    End If
End Sub

Where ever the workbook is located, place the wave file in the same location. You'll need to change the wave file name to match your's.

 

[VinceF]

Logit,

Thank you so much...it works perfectly...

It was saying "code must be updated for use on 64 bit system so I inserted PtrSafe in front of Function ""Private Delclare PtrSafe Function"" and it worked.
Only issue is that upon resetting and shutting down the workbook it plays the .wav file 5 or 6 times... I could live with this as the sheet is shutting down... unless there's an easy fix.

Thanks again...you guys are great...!