Display Picture & Play Sound for 5 seconds

[sisterwolf]

I have a form/spreadsheet that I want to run a macro on that when the user fills in their name in D4 on the Report tab, that tab disappears and the Halloween tab with scary picture and sound are shown and played for 5 seconds and then the Report tab is brought back into view. I've included the report with this posting. I have not yet purchased the .wav file but I do have samples included below the picture.

I am still very new to VBA and have been having a difficult time finding the code for this trick to treat our hard-working field guys on Halloween. Would someone please help me with this code?

Questions:
1- How do I get the code to run automatically? I only know how to get it to work via a button or stepping manually through the code.
2- How do I embed the .wav sound into the workbook? I've found a few practice sounds and have attached the links at the bottom of the hidden Halloween tab.
3- What is the proper code for the 5-second timeframe?

[VBA]
Sub ShowHalloween()

'When Field Tech types his name into cell D4 show Halloween tab and play sound file for 5 seconds
If Sheets("Daily Field Report").Range("D4") <> "" Then

Sheets("Happy Halloween").Visible = True
Sheets("Daily Field Report").Visible = False

'With Application
'.OnTime Now + TimeValue("00:00:05") ... show Halloween tab for 5 seconds

'Play sound file {LongGhostScream (0:04)} ... want to embed sound into workbook

'End With

Sheets("Daily Field Report").Visible = True
Sheets("Happy Halloween").Visible = False

End If

End Sub
[/VBA]

[Kenneth Hobs]

Do you want just one sound file to play or a random selected file to play?

Will the sound file(s) be downloaded to a specific location first or download from the internet as needed?

[sisterwolf]

I need to have the sound file attached/embedded into the workbook...if that's possible.

[Kenneth Hobs]

This method without modification, only works with the embedded wave files. Once you insert the sound file to your hidden sheet, notice the name in the name box. Change it if you like. You will need to modify the "Object 2" possibly.

You may also need to consider setting the zoom.

In a module:
[VBA]Sub MakeEmScared()
Dim tNow As Date

Worksheets("Happy Halloween").Visible = True
Worksheets("Daily Field Report").Visible = False
Application.ScreenUpdating = False

'Play sound file once
'Worksheets("Happy Halloween").OLEObjects("Object 2").Verb 'Set object name as needed.

tNow = Now + TimeValue("00:00:05")
Do Until tNow < Now
'Play many times
Worksheets("Happy Halloween").OLEObjects("Object 2").Verb 'Set object name as needed.
DoEvents
Loop

Worksheets("Daily Field Report").Visible = True
Worksheets("Happy Halloween").Visible = False
Application.ScreenUpdating = True
End Sub[/VBA]

Right click your Daily Field Report sheet and View Code. Then paste this:
[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range
Set r = Intersect(Range("D4"), Target)
If r Is Nothing Then Exit Sub

MakeEmScared

Set r = Nothing
End Sub
[/VBA]

[sisterwolf]

The code works great! Thanks a lot for your help, Kenneth. Everyone in the office loves it. Now to see how scared this will make our guys out in the field.