How do I create a sound alert in excel spreadsheet

[Guest]

How do I create a sound alert, to use ,for when a cell value changes to a
given value. ie: I am importing realtime data and want a sound to notify when
when a certain value is detected

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
Beep
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi Bob

Thanks alot, sure its just what I want, but a little over my head

Have pasted code as you said, was the code to start at

private and end at end sub

the cell range I am monitoring is d2:d4, and the value I want to trigger the
alert is 0 ( zero). Where is this entered into the code you said paste

Cheers again Shaun

 

[Bob Phillips]

Shaun,

This is modified to your particular needs. Follow the instructions after the
previous piece of code so as to know where to put it

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("D24")) Is Nothing Then
With Target
if .value = 0 Then Beep
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi again Bob

Have done as you have instructed

When the code is pasted, what do I do with the headins in the two drop downs
above

The first has in it WORKSHEET and GENERAL

The second has CHANGE ACTIVATE etc and several others

The reason I am asking as the code does not generate a BEEP when the value
is 0

Cheers Shaun

 

[Bob Phillips]

Shaun,

You don't need to do anything with them, it is already effectively done.

The beep will only happen when any value in D24 is changed to 0.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

hi!
Did you found answer?

 

[Ryan Jones]

You can use a beep command in the sheet code under change

 

[SOLI]

HI BOB
I AM USING THE SUGGESTION YOU PRINTED FOR SHAUN T AND IT WORKS FINE. HOW CAN
I CHANGE THE BEEP SOUND TO A CONTINUOUS SOUND OR MUSIC CLIP UNTIL I STOP IT
ACTIVELY?
FOR THE VALUE " 0 " , HOW CAN I MAKE THE MACRO USE THE CONTENT OF A GIVEN
CELL INSTEAD? THAT WAY I WILL BE ABLE TO CHANGE THE 0 VALUE WITHOUT HAVING TO
ENTER THE VBA CODE EVERY TIME.TKS.
SOLI
(e-mail address removed)