Playing a sound on cell value

graemeal

graemeal

Active Member
Joined
May 17, 2007
Messages
316
Platform
  1. Windows
I have an automated sort descend happening all day. The top several cells are coloured green and if a cell jumps to the top from below the green cells, I would love a warning sound. Is is possible to activate a sound if a cell changes from a colour to a non colour. Preferably if any of the cells within a range of say F2: F6 changes from green to a non colour.

If it is not possible to have a change of colour to activate the macro, I could get around it by - if any cells in a range of H2:H5 >than 1 it plays a sound

Cheers

XP XL2003
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
You haven't said what the trigger is for a cell to change colour - a Conditional Format? If so what value triggers a change of colour?

Anyway, try this. Right click the sheet tab, select View Code and paste in

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

Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000


Private Sub Worksheet_Change(ByVal Target As Range)
Const FName As String = "C:\Intel.wav"
If Not Intersect(Target, Range("F2:F6")) Is Nothing Then
    Call PlaySound(FName, 0&, SND_ASYNC Or SND_FILENAME)
End If
End Sub

Change "C:\Intel.wav" to the full path of the .wav file that you want to play.

Now if there is any change in cells F2:F6 a sound should be played.
 
Upvote 0
User warnings should always be visual first. What if the user's sound is on mute or turned really low (or what if they don't have a soundcard on their machine)? ...unless you also plan on overriding their volume control as well...

At work I always have mine on mute since I don't particularly enjoy hearing all those beeps, clicks, and whistles.
 
Upvote 0
Thank you all kindly,

I have about 6 columns or so about 1930 rows deep. I have autofilter activated so all the rows sort descend together. A macro automatically sort descends constantly and brings the biggest figure in a column to the top. I basically colour the top 10 cells green so I can visually see when a cell below the top green ones flys to the top. But that requires staring at the screen non stop 6 hours per day. So I would love to have an alert when a cell from below the top green coloured ones jumps to the top. But as I said if not possible by colour change I can add a column and get to work when say a cell changes from zero to 1.....or > than 1. (same thing)

Cheers
 
Upvote 0
But as I said if not possible by colour change I can add a column and get to work when say a cell changes from zero to 1.....or > than 1. (same thing)
Click to expand...

Please :)

then tell us what the range is.
 
Upvote 0
Thanks to all. Works like a dream. My work load in a flash has been reduced by 6 hours per day.

Brilliant. Thanks again
 
Upvote 0
Thanks Vog. Your code works like a dream. But if I wanted to activate a sound also if I7:I18 cells change what do I add to your code. Tried a few things but error comes up.

So if F2:F6 and or I7:I18 cells change it activates the sound wave. Ideally would be great to have 2 differant sounds for each range. one sound for F2:F6 and another for I7:I18

Thanks for your help
 
Upvote 0
Try

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim FName As String
If Not Intersect(Target, Range("F2:F6")) Is Nothing Then
    FName = "C:\Intel.wav"
    Call PlaySound(FName, 0&, SND_ASYNC Or SND_FILENAME)
ElseIf Not Intersect(Target, Range("I7:I18")) Is Nothing Then
    FName = "C:\Tardis.wav"
    Call PlaySound(FName, 0&, SND_ASYNC Or SND_FILENAME)
End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
Calculating coloured/uncoloured cells in a table
Replies
0
Views
65
vikosh
V
A
  • Solved
Conditional Formatting - apply rules to multiple rows based on specific cells (drop down menus)
Replies
4
Views
684
AndyB86
A
R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
438
rahildhody
R
E
  • Question Question
Specific VBA Function
Replies
0
Views
260
EAlexWolfe
E
N
  • Solved
Pulling certain row values if a number is matched
Replies
2
Views
378
Newbie73
N

Forum statistics

Threads
1,224,550
Messages
6,179,463
Members
452,915
Latest member
hannnahheileen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top