+ Reply to Thread
Results 1 to 20 of 20

Audio alert

  1. #1
    Registered User
    Join Date
    10-12-2008
    Location
    Melbourne, Australia
    Posts
    55

    Audio alert

    Hello all,

    I am trying to add a sound alert to a junior athletics recording program. The date of the meetings is in column (A), results are entered in column (B) for a particular event, say 100m sprint. Column (C) & (D) contain formula that recognise if that athletes result is a personal best (PB) result or a club record (CR). I am after a code that will play a sound when a new PB or CR has been achieved. I have downloaded the sounds I want into the office media file. I am new to VB codes. Any suggestions?
    Last edited by RunHard; 11-03-2008 at 12:45 AM. Reason: SOLVED

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello RunHaArd,

    Welcome to the Forum!

    The solution to this 2 fold. First, you need a macro to play the sound file. Second, you need to have a macro in the worksheet's Worksheet_Change() event procedure to recognize when the sound should be played. I would need to know more about what triggers a change in columns "C" and "D" before I can write the code for that part. In the mean time, you can add this to your workbook.

    Play Sound Macro - Place in a VBA Module
    Please Login or Register  to view this content.
    Adding the Macro
    1. Copy the macro above pressing the keys CTRL+C
    2. Open your workbook
    3. Press the keys ALT+F11 to open the Visual Basic Editor
    4. Press the keys ALT+I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Paste the code by pressing the keys CTRL+V
    7. Make any custom changes to the macro if needed at this time.
    8. Save the Macro by pressing the keys CTRL+S
    9. Press the keys ALT+Q to exit the Editor, and return to Excel.

    Sincerely,
    Leith Ross

  3. #3
    Registered User
    Join Date
    10-12-2008
    Location
    Melbourne, Australia
    Posts
    55

    Audio Alert

    Thanks Leith,

    I have inserted the code into the standard module as per your instructions.

    Before I continue re your query about triggers I must amend my first post re my column set up which should read Col B - Date Col C - recorded time/distance Col D - Formula re PB and Col E - Formula re CR. There are up to 15 events per worksheet with each event taking up three columns as per the above explanation. Each event follows so 100m sprint takes up Col C,D,E 200m sprint F,G,H etc etc.

    Triggers.

    What triggers a change in column D which is Personal Best (PB). When a time or distance is entered in Col C and this entry is a better recorded time or distance than the previous recorded time or distance then the formula places a "PB" in col D. Adjacent to the record in Col C.

    Sample Formula being =IF(ISBLANK(C10)," ",IF(ISTEXT(C10)," ",IF(C10<(MIN(C$9:C9)),"PB"," ")))

    What triggers a change in column E which is Club Records (CR). When a time or distance is better than a value recorded in cell C7 (The Club Record) then the formula places a "CR" in column E. Adjacent to the time/distance in Col C.

    Sample formula being =IF(ISBLANK(C9)," ",IF(ISTEXT(C9)," ",IF(C9<=MIN(C$7:C7),"CR"," ")))

    The range that would need to respond to the sound alert would be D9:D30 for PB and E9:E30 for CR. Or for a full worksheet covering all events D9:AF30 (or even greater)

    This maybe confusing. I tried to attach a copy of one worksheet but failed. Let me know if I have confused the issue.

    Thank you for your time Leith

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello RunHard,

    This will verify changes in columns "D" and "E" for rows 9 to 30. The CR and PB are not case sensitive. Right now I have two standard sound files in the macro (marked in red). You need to add the file path and name for your sound files to play.
    Please Login or Register  to view this content.
    How to Save a Worksheet Event Macro
    1. Copy the macro using CTRL+C keys.
    2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
    3. Left Click on View Code in the pop up menu.
    4. Paste the macro code using CTRL+V
    5. Make any custom changes to the macro if needed at this time.
    6. Save the macro in your Workbook using CTRL+S

    Sincerely,
    Leith Ross

  5. #5
    Registered User
    Join Date
    10-12-2008
    Location
    Melbourne, Australia
    Posts
    55
    Leith,

    Forgive my ignorance as I am new to this. Do I remove the green writing in the code. I have pasted everything including this and at present it is not working.


    Thanks

    Runhard

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Green writing in the code is only notes, which you should really use to remind you of what the code does. Anything preceded by an apostrophe (') is not actual code.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  7. #7
    Registered User
    Join Date
    10-12-2008
    Location
    Melbourne, Australia
    Posts
    55
    Thanks RoyUK,

    Leith,

    Got the codes in and when I enter data I got a Compile error Ambiguos name detected:Playsoundfile


    Runhard

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello RunHard,

    Post your workbook. I'll review the macros and make any needed corrections.

    Sincerely,
    Leith Ross

  9. #9
    Registered User
    Join Date
    10-12-2008
    Location
    Melbourne, Australia
    Posts
    55
    Hello Leith,

    I have attached a sample worksheet with the code as the workbook was too big to upload. I hope this will suffice.

    Thanks for your paitence.


    Runhard
    Attached Files Attached Files

  10. #10
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello RunHard,

    There were some error from copying the macro. Those have been fixed. The macro has been expanded to include all the events. You will need to change the the sound file paths in the macro to the access the your sound files. I have marked them in red. The macro has been installed the attached workbook and checked.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    10-12-2008
    Location
    Melbourne, Australia
    Posts
    55
    Thanks Leith,

    I am about to finish work. When I get home I will try it in my workbook after which I will post the results.

    Thank you so much for your time.

    Runhard

  12. #12
    Registered User
    Join Date
    10-12-2008
    Location
    Melbourne, Australia
    Posts
    55
    Leith,


    I have transferred the code however when I enter data I get the Compile error: Sub or function not defined - indicating the Playsoundfile in the worksheet code. The only thing I changed in the code was the wav file location.

    Sorry to drag this out.


    Runhard

  13. #13
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    PlaySoundFile is a sub in Module1 in the workbook Leith posted.
    Entia non sunt multiplicanda sine necessitate

  14. #14
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello RunHard,

    If you are using the workbook I posted then you shouldn't be getting any compile errors. If you copied the macro from the post, as SHG pointed out, Module1 contains the API code macro to play the sound files.

    Sincerely,
    Leith Ross

  15. #15
    Registered User
    Join Date
    10-12-2008
    Location
    Melbourne, Australia
    Posts
    55
    Thanks Leith & SHG,

    I did have the code in the wrong Module. (Very new to this). I have since rectified this issue and I can know input data without any error notification. Still no sound alert though. I have not changed the wav files as yet but my computer has the same wav files, that are in the code, in the same location so it should work. Is is possible that the formula's for the PB's and CR's in the columns is affecting the sound alert?

    I feel like I am getting closer to my desired result. I hope your patience holds out.

    Thanks

    Runhard (Sheet with formulas and sound alert code attached)
    Attached Files Attached Files

  16. #16
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello RunHard,

    I downloaded the file and it ran on my machine with sound. I am attaching it to this post under a different name. Open this workbook and seeif the problem persists. If so, there are only a few things that could cause this problem.

    1) Sound volume is off or speakers not connected.
    2) Sound File location is incorrect.
    3) The Windows MultiMedia Dynamic Link Library (winmm.dll) is damaged. This would prevent system sounds from playing.

    The API call is used in Windows '95 to '2003. So, I don't that the API is the cause.

    Sincerely,
    Leith Ross
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    10-12-2008
    Location
    Melbourne, Australia
    Posts
    55
    Leith,

    Just prior to receiving your reply I manually entered a PB & CR in the columns and this generated the sound alert. However when I entered a time in the event column that then generated the PB notation by way of formula, there was no sound alert. Does that make sense?

    Runhard

  18. #18
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello RunHard,

    Ah, the missing piece of the puzzle! I misunderstood how you wanted this to trigger. The macro has been corrected to only play whenever the time is a Personal Best or Club Record as determined by the formula. Manual entries are ignored. The macro below has been added to the workbook.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    10-12-2008
    Location
    Melbourne, Australia
    Posts
    55
    Leith,

    I have just added the new code and it is working a treat. Can I now just take this opportunity to thank you so much for your patience and guidance. The sound alert was the last piece of the puzzle and now the Junior Athletics recording program is complete. It will be a thrill for the kids to hear applause when they achieve a personal best. Through the sharing of your knowledge as well as the availabilty of this site I have been able to complete this task with very little experience. I have learned so much and enjoyed the site.

    Thank you so much once again.

    A very happy Runhard.

    PS I will now endeavor to add 'Solved' to this post if I can.

  20. #20
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello RunHard,

    Thank you for the opportunity to help with this project. Maybe you can make a mpeg and post it here of the kids when you post their times. Thanks again for the kind words.

    Sincerely,
    Leith Ross

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1