| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Gerard Sanchez
Guest
Posts: n/a
|
'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
Guest
Posts: n/a
|
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 > > > |
|
||
|
|
||||
|
Gerard Sanchez
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:20796D3B-14A7-4D13-BAA6-(E-Mail Removed)... > 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 >> >> >> |
|
||
|
|
||||
|
Per Jessen
Guest
Posts: n/a
|
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" <(E-Mail Removed)> skrev i meddelelsen news:%(E-Mail Removed)... > > 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" <(E-Mail Removed)> wrote in message > news:20796D3B-14A7-4D13-BAA6-(E-Mail Removed)... >> 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 >>> >>> >>> > > |
|
||
|
|
||||
|
Gerard Sanchez
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... > 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" <(E-Mail Removed)> skrev i meddelelsen > news:%(E-Mail Removed)... >> >> 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" <(E-Mail Removed)> wrote in message >> news:20796D3B-14A7-4D13-BAA6-(E-Mail Removed)... >>> 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 >>>> >>>> >>>> >> >> > |
|
||
|
|
||||
|
Per Jessen
Guest
Posts: n/a
|
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" <(E-Mail Removed)> skrev i meddelelsen news:(E-Mail Removed)... > 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" <(E-Mail Removed)> wrote in message > news:(E-Mail Removed)... >> 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" <(E-Mail Removed)> skrev i meddelelsen >> news:%(E-Mail Removed)... >>> >>> 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" <(E-Mail Removed)> wrote in message >>> news:20796D3B-14A7-4D13-BAA6-(E-Mail Removed)... >>>> 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 >>>>> >>>>> >>>>> >>> >>> >> > > |
|
||
|
|
||||
|
Gerard Sanchez
Guest
Posts: n/a
|
Hi Per
Good Morning :-) Pasted the code just now and it didn't produce any dind sound for any of the pairs. ?? . . . "Per Jessen" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... > 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" <(E-Mail Removed)> skrev i meddelelsen > news:(E-Mail Removed)... >> 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" <(E-Mail Removed)> wrote in message >> news:(E-Mail Removed)... >>> 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" <(E-Mail Removed)> skrev i meddelelsen >>> news:%(E-Mail Removed)... >>>> >>>> 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" <(E-Mail Removed)> wrote in message >>>> news:20796D3B-14A7-4D13-BAA6-(E-Mail Removed)... >>>>> 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 >>>>>> >>>>>> >>>>>> >>>> >>>> >>> >> >> > |
|
||
|
|
||||
|
Per Jessen
Guest
Posts: n/a
|
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" <(E-Mail Removed)> skrev i meddelelsen news:(E-Mail Removed)... > Hi Per > > Good Morning :-) > > Pasted the code just now and it didn't produce any dind sound for any of > the pairs. ?? . . . > > > > > "Per Jessen" <(E-Mail Removed)> wrote in message > news:(E-Mail Removed)... >> 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" <(E-Mail Removed)> skrev i meddelelsen >> news:(E-Mail Removed)... >>> 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" <(E-Mail Removed)> wrote in message >>> news:(E-Mail Removed)... >>>> 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" <(E-Mail Removed)> skrev i meddelelsen >>>> news:%(E-Mail Removed)... >>>>> >>>>> 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" <(E-Mail Removed)> wrote in message >>>>> news:20796D3B-14A7-4D13-BAA6-(E-Mail Removed)... >>>>>> 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 >>>>>>> >>>>>>> >>>>>>> >>>>> >>>>> >>>> >>> >>> >> > > |
|
||
|
|
||||
|
Gerard Sanchez
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... > 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" <(E-Mail Removed)> skrev i meddelelsen > news:(E-Mail Removed)... >> Hi Per >> >> Good Morning :-) >> >> Pasted the code just now and it didn't produce any dind sound for any of >> the pairs. ?? . . . >> >> >> >> >> "Per Jessen" <(E-Mail Removed)> wrote in message >> news:(E-Mail Removed)... >>> 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" <(E-Mail Removed)> skrev i meddelelsen >>> news:(E-Mail Removed)... >>>> 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" <(E-Mail Removed)> wrote in message >>>> news:(E-Mail Removed)... >>>>> 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" <(E-Mail Removed)> skrev i meddelelsen >>>>> news:%(E-Mail Removed)... >>>>>> >>>>>> 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" <(E-Mail Removed)> wrote in message >>>>>> news:20796D3B-14A7-4D13-BAA6-(E-Mail Removed)... >>>>>>> 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 >>>>>>>> >>>>>>>> >>>>>>>> >>>>>> >>>>>> >>>>> >>>> >>>> >>> >> >> > |
|
||
|
|
||||
|
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Apply macro | =?Utf-8?B?SGFzc2Fu?= | Microsoft Excel Programming | 2 | 22nd Sep 2007 12:14 PM |
| VBA AutoFilter how to apply 2 ranges for selection to copy | =?Utf-8?B?RGVubmlz?= | Microsoft Excel Misc | 1 | 10th Nov 2004 11:41 PM |
| Named ranges & protect Print_area? & apply changes across WS's? | Steven | Microsoft Excel Discussion | 3 | 19th Apr 2004 10:14 PM |
| Apply Filter Macro | Lani | Microsoft Access Database Table Design | 0 | 29th Mar 2004 11:42 PM |
| Re: HELP! Macro to apply a style | Fredg | Microsoft Access Getting Started | 0 | 23rd Jul 2003 02:27 PM |
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc. |
