| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply Macro To Other Ranges
'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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Apply Macro To Other Ranges
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. -- Regards, OssieMac "Gerard Sanchez" wrote: '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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Apply Macro To Other Ranges
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. "OssieMac" wrote in message ... 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. -- Regards, OssieMac "Gerard Sanchez" wrote: '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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Apply Macro To Other Ranges
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" skrev i meddelelsen ... 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. "OssieMac" wrote in message ... 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. -- Regards, OssieMac "Gerard Sanchez" wrote: '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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Apply Macro To Other Ranges
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" wrote in message ... 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" skrev i meddelelsen ... 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. "OssieMac" wrote in message ... 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. -- Regards, OssieMac "Gerard Sanchez" wrote: '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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Apply Macro To Other Ranges
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" skrev i meddelelsen ... 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" wrote in message ... 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" skrev i meddelelsen ... 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. "OssieMac" wrote in message ... 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. -- Regards, OssieMac "Gerard Sanchez" wrote: '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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Apply Macro To Other Ranges
Hi Per
Good Morning :-) Pasted the code just now and it didn't produce any dind sound for any of the pairs. ?? . . . "Per Jessen" wrote in message ... 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" skrev i meddelelsen ... 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" wrote in message ... 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" skrev i meddelelsen ... 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. "OssieMac" wrote in message ... 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. -- Regards, OssieMac "Gerard Sanchez" wrote: '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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Apply Macro To Other Ranges
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" skrev i meddelelsen ... Hi Per Good Morning :-) Pasted the code just now and it didn't produce any dind sound for any of the pairs. ?? . . . "Per Jessen" wrote in message ... 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" skrev i meddelelsen ... 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" wrote in message ... 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" skrev i meddelelsen ... 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. "OssieMac" wrote in message ... 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. -- Regards, OssieMac "Gerard Sanchez" wrote: '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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Apply Macro To Other Ranges
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 "Per Jessen" wrote in message ... 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" skrev i meddelelsen ... Hi Per Good Morning :-) Pasted the code just now and it didn't produce any dind sound for any of the pairs. ?? . . . "Per Jessen" wrote in message ... 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" skrev i meddelelsen ... 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" wrote in message ... 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" skrev i meddelelsen ... 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. "OssieMac" wrote in message ... 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. -- Regards, OssieMac "Gerard Sanchez" wrote: '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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| macro apply to all worksheet | Excel Worksheet Functions | |||
| Apply macro to one sheet only | Excel Discussion (Misc queries) | |||
| Apply a macro to only one sheet | Excel Programming | |||
| Apply macro | Excel Programming | |||
| Apply a Macro to a FunctionKey... | Excel Programming | |||
Linear Mode
