Apply Macro To Other Ranges

[Gerard Sanchez]

'Hi,

'I was wondering how to apply this code to also apply to ranges:
'H102 & H103 . . .H154 & H155 . . .H206 & H207 . . . and so on . . .

'tried to cut and paste the code from Private Sub to End Sub changing only
the Ranges (i.e. above)
'but got an error message "Ambiguous Name Detected:
Workbook_SheetCalculate"


Private Declare Function sndPlaySound32 Lib "winmm.dll" Alias _
"sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Static OnlyOnce As Boolean

If OnlyOnce Then Exit Sub
If Range("H51").Value = "" Or Range("H51").Value = 0 Then Exit Sub
If Range("H50").Value = Range("H51") Then
sndPlaySound32 "ding", 1&
OnlyOnce = True
End If

End Sub

 

[OssieMac]

Hi Gerard,

"Ambiguous Name Detected" is having more than one sub with the same sub name
and since events must have specific sub names then you can only have one
anyway.

What conditions decide which range is to be processed? Is it the actual
range being changed to cause calculation to run?

I am wondering if the calculate event is the best option to trigger your
code so can you give us a little more information on what occurs leading up
to triggering the event. I am assuming that you are after an audible signal
when certain conditions occur.

 

[Gerard Sanchez]

Values are manually keyed into the ranges. i.e., H50 & H51. And to play a
sound when both values are equal.
Nothing really occurs before the event, It's just a person entering values
into the cells.

I'be already had beep fuction for values that are not equal at some other
part of the worksheet. However,
on cells H102 & H103 . . .H154 & H155 . . .H206 & H207 . . . and so on . .
I'd like to hear a ding sound whenever
each pair of cells named above are equal.

 

[Per Jessen]

Hi Gerald,

As user enter values in the cells you want to test, I would use a worksheet
change event.

I assume that you want to look at column H and if the value in an even row
number is equal to the value in the cell below. If the two cells are equal
you will hear the ding sound.

This should do it:

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Intersect(Target, Range("H:H"))

If Not isect Is Nothing Then
If Target.Row Mod 2 = 0 Then
If Target.Value = "" Or Target.Value = 0 Then Exit Sub
If Target.Value = Target.Offset(1, 0).Value Then
sndPlaySound32 "ding", 1&
End If
Else
If Target.Value = "" Or Target.Value = 0 Then Exit Sub
If Target.Value = Target.Offset(-1, 0).Value Then
sndPlaySound32 "ding", 1&
End If
End If
End If
End Sub

Regards,
Per

 

[Gerard Sanchez]

Hi,

I assume that you want to look at column H and if the value in an even row
number is equal to the value in the cell below. If the two cells are equal
equal you will hear the ding sound.

Sorry, I may not have been clear in my question, but I actually just needed
the ding sound specifically only to these specified cell ranges:

H50 & H51
H102 & H103
H154 & H155
H206 & H207
H310 & H311

The ding sound notifies the user when specified cells have the same value.
and would set off even user types in value on some other part of the sheet.

Please help?

Many thanks!

--Gerard



Uhmm

 

[Per Jessen]

Hi Gerad

No problem:

Private Sub Worksheet_Change(ByVal Target As Range)
Set TargetRange = Range("H50:H51,H102:H103,H154:H155,H206:H207,H310:H311")

Set isect = Intersect(Target, TargetRange)

If Not isect Is Nothing Then
If Target.Row Mod 2 = 0 Then
If Target.Value = "" Or Target.Value = 0 Then Exit Sub
If Target.Value = Target.Offset(1, 0).Value Then
sndPlaySound32 "ding", 1&
End If
Else
If Target.Value = "" Or Target.Value = 0 Then Exit Sub
If Target.Value = Target.Offset(-1, 0).Value Then
sndPlaySound32 "ding", 1&
End If
End If
End If
End Sub

Regards,
Per

 

[Gerard Sanchez]

Hi Per

Good Morning

Pasted the code just now and it didn't produce any dind sound for any of the
pairs. ?? . . .

 

[Per Jessen]

Hi Gerad

Good evening

It's working here...

Did you pasted to the code sheet for the desired sheet ?

I assume you have included the "Private Declare Function..." in same code
sheet, as you would recive an error other wise.

Regards,
Per

 

[Gerard Sanchez]

Wow! The Private Declare Function was on the Workbook Module, now that its
on the same workbook it's PERFECT. Exactlly what I was looking for.
Thank you soooo much! Per for taking the time to help the helpless

Many Many thanks!

--Gerard