Use of Wav Files with a Spreadsheet

[golf4]

Hi, again, everyone -

Just a quick question regarding the use of wav files within a spreadsheet project. I went ahead and borrowed --- yea, that's it: BORROWED --- some code that allows me to tie wavs files to certain actions taken by the user(s) of my spreadsheet. The code below is set to play the wav file called "Rickmeid" when the user enters an "X" in cell H20 of the Data_Entry_Sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("H20")) Is Nothing Then
Exit Sub
Else

If Intersect(Target, Range("H20")) = "X" Then

Call PlaySound("H:\rickmeid.wav", 0)

End If
End If
End Sub

I was hoping someone could provide me a little help in [1] relocating the wav file into the spreadsheet sheet called WAVS from the user's network (H) drive and [2] how I would modify the code to active the wav file from the WAVS sheet instead of from the user's network (H) drive. I could, probably, locate the wav(s) on the user's C drive, but would rather have the wavs in the spreadsheet itself.

Any assistance you can provide would be great.

Thanks again,

Golf

[DRJ]

First insert the wav file

Insert | Object | From File

Then use this to play it

ActiveSheet.Shapes("Object 1").Select
Selection.Verb Verb:=xlPrimary

[golf4]

Thanks for the response, Jacob -

Just a clarification - using your suggestion, would my code by modified as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("H20")) Is Nothing Then
Exit Sub
Else

If Intersect(Target, Range("H20")) = "X" Then

ActiveSheet.Shapes("rickmeid.wav").Select
Selection.Verb Verb:=xlPrimary

End If
End If
End Sub

Is this correct?

Thanks

[DRJ]

It should work fine. You can change your code to this though:

Private Sub Worksheet_Change(ByVal Target As Range) 

    If Not Intersect(Target, Range("H20")) Is Nothing And Intersect(Target, Range("H20")) = "X" Then
        ActiveSheet.Shapes("rickmeid.wav").Select 
        Selection.Verb Verb:=xlPrimary 
        Else
    End If

End Sub

[golf4]

Hi, Jacob -

I tried your suggested code replacement, and received the following run-time error:

"Run-Time Error '-2147024809 (80070057) The item with the specified name wasn't found".

I did the Insert - Object - From File and placed it on the WAVS sheet. Does it have to be inserted on the Data_Entry_Sheet?

Thanks

[DRJ]

You need to put the wav file on the active sheet. Or activate the sheet that it is on for the code to work.

[DRJ]

Just put the file on the data entry sheet and format it with no lines and no fill and it will be invisible, then just stick it somewhere way out of the way.

[golf4]

Hi, Jacob -

Ya know: I looked at the :Name" field in the upper left hand corner of the WAVS worksheet and noticed that the wav file was named "Object 3". I tried inserting "Object 3" instead of the wav name and received a different run time error:

"Run Time Error '438': Object doesn't support this property or method".

...... drivin me insane!!!

Thanks

[DRJ]

Make sure the sheet that it is on is active

Sheets("Data Sheet").Activate
ActiveSheet.Shapes("Object 3").Select
Selection.Verb Verb:=xlPrimary

[golf4]

Hi, Jacob -

FINALLY!!!!! I was able to get it to work....

I removed the lines and the file, but the "little speaker icon" remains. I can figure out how to hide it (maybe under a clear textbox) because I want to have Data_Entry_Sheet protected and the wav file won't work if both the sheet and wav file is locked. THE FUN NEVER STOPS!!!

Thanks so much for the help,

Golf