Play Sound In Excel
Dan from Wauconda posed this week's Excel question.
I have an Excel worksheet that calculates a value. If the cell value is > 100 I need a midi file to be played.
This tip makes use of the event handlers discussed here on December 23rd. If you are not familiar with event handlers, please review that tip first.
The first step is to paste a copy of the MIDI file into an out-of-the-way spot on your worksheet.
- Open the WIN95 media player. Programs > Accessories > Multimedia
- Open the chosen midi file in the media player.
- In the media player, Select Edit, then Copy object.
- In Excel, find an out-of-the way location. In this example, I am picking Sheet1 Cell Z99. Click here and then hit Edit > Paste
- Look at the name box in Excel. In the name box, it will give the midi file a name. Mine is called Object 1.
The VBA macro to play that midi file is:
Sub Playit() ActiveSheet.Shapes("Object 1").Select Selection.Verb Verb:=xlPrimary End Sub
Depending on if you have Excel 97 or Excel 95/7.0 you would use the appropriate event handler to invoke the above code. Here is an example for Excel 97. Assume if a new value is entered in cell A1, you want to check if the calculated cell in A2 is > 100. The event handler is as follows:
Open the Visual Basic Editor. In the left window, right click Sheet1 and select View Code. At the top of the Book1 - Sheet1 Code dialog box, there are two dropdowns. From the left dropdown select Worksheet. From the right dropdown, select Change. Enter the following lines of code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$A$1" Then If Range("A2") > 100 Then ActiveSheet.Shapes("Object 1").Select Selection.Verb Verb:=xlPrimary End If End If End Sub