Using Multiple Tests and Sounds With Worksheet Macros

[Guest]

I am currently using the following to generate a ringing sound if cell B13 is
greater than cell B14 and it works fine. I cannot find a way to get it to
perform a second logit test and make second sound (chimes) if the two cells
are equal. Is there a way to do this? Thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Range)
If Sheets("Sheet2").Range("b13") > Sheets("Sheet2").Range("b14") Then
Application.Run "Ringing"
End If
End Sub

 

[Guest]

Why not either add an ElseIf or a second If Then statement in there?
Pseudo-code here to keep the lines short:

If Sheets("1").Range("B13)>Sheets("1").Range("B14) Then
Application.Run "Ringing"
ElseIf Sheets("1").Range("B13")=Sheets("1").Range("B14") Then
Application.Run "ChimesInstead"
End if

or add this below the current code

If Sheets("1").Range("B13")=Sheets("1").Range("B14") Then
Application.Run "ChimesInstead"
End if

 

[Dave Peterson]

Since you're using the worksheet_change event, you may even want to test to see
if the change was made to B13 or B14.

And I'm assuming that sheet2 is the sheet that holds the code.

Private Sub Worksheet_Change(ByVal Target As Range)
dim myRng as range
set myrng = me.range("b13,B14")

if intersect(target,myrng) is nothing then exit sub

If me.Range("b13") <> me.Range("b14") Then
'different
call Ringing
else
'same
call whateverroutineyouwant
End If
End Sub

You don't need to use application.run to call a routine in the same project.

 

[Guest]

Thanks very much to both of you. This all works great!