asked on

Play audio file

Hi Experts,

I would like to request Experts help. Is that possible to play an audio file for 2 minutes that was resided at folder path (D:\AudioFile\Ring.mp3) when the data row at DataSheet highlighted in yellow color due to conditional format: “(=AND($I7+TODAY()<=NOW()+TIME(0,5,0),$I7+TODAY()>NOW())”

Hope Experts will help me to add this feature in the attached workbook.

Audio.xls

TommySzalapski

The easiest way would be to convert the sound file to a 2 minute .wav file and to this:

Put this at the top of the module

Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long

Open in new window

Then in the "UpdateClock" function you can check for the yellow and play the sound

If Range("I7").Interior.Color = 65535 Then
  PlaySound "D:\AudioFile\Ring.wav", 0, 0
End IF

Open in new window

TommySzalapski

Or use this code and you can leave the sound short

If Range("I7").Value + Date >= Now And Range("I7").Value + Date < Now + TimeSerial(0, 2, 0) Then
  PlaySound "D:\AudioFile\Ring.wav", 0, 0
End If

Open in new window

TommySzalapski

But it should still be a wave file.
The TimeSerial(0, 2, 0) is how you tell it how long to play
TimeSerial(hours, minutes, seconds)

Theva

ASKER

Hi Tommy,

Shows error as "Sub or function not defined" at this line:

"PlaySound"

Please assist.

TommySzalapski

Put this code at the top of the module that uses PlaySound

Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long

Open in new window

TommySzalapski

Not the top of the Sub, the top of the whole thing.

TommySzalapski

Like this: (remember it only works on .wav files)
Audio.xls

Theva

ASKER

Hi,

Have tested with .wav file. The audio file wasn’t activated when row turns into yellow. Do I need to activate any reference?

TommySzalapski

Sorry, the if line should read:

If Range("I7").Value + Date >= Now + TimeSerial(0, 3, 0) And Range("I7").Value + Date < Now + TimeSerial(0, 5, 0) Then

It will keep playing the file over and over for two minutes.

Theva

ASKER

Hi,

Have tested, the file still not active.

ASKER CERTIFIED SOLUTION

TommySzalapski

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Theva

ASKER

Hi,

Its not for me, himmmm! What when wrong. Does this got something to do with windows (I’m using Windows7). The color coding mainly to match with our internal workflow.

TommySzalapski

Can you play a file from code at all? Try just running the PlaySound function and see if anything plays. Also try running the same file from Windows (I have Win7 also)

Theva

ASKER

Hi,

Please advice how to test only the PlaySound function? sorry for the trouble.

Theva

ASKER

Hi,

It works after I save the file at C:\

Theva

ASKER

Hi,

Thanks for the great support and help.

Theva

ASKER

Hi Tommy,

Need your help with this Q, it was created by you and requiring an amendment, hope you will consider:

https://www.experts-exchange.com/questions/26639428/Rewrite-Conditional-Formatting.html