|
Application events
I have an App_sheetchange sub that catches changes to all worksheets and
decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. |
Application events
As a guess you have a recursive call. Each time the change event is fired the
procedure that runs makes an change calling the Change event to fire again. And round round it will go. You can use. Application.EnableEvents = False 'at the start of the code and Application.EnableEvents = True 'at the End of the code To turn events off while the procedure runs. -- HTH... Jim Thomlinson "ben" wrote: I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. |
Application events
Ben,
Your macro is probably triggering events that in turn call event macros.... You need to turn those events off! (I usually include this in all my subs) Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) Application.Enableevents = false If resaleactive = 0 Then Exit Sub worksheetchange wks, target Application.Enableevents = true End Sub Sub worksheetchange(wks As Worksheet, target As Range) Application.Enableevents = false MsgBox target.AddressLocal Application.Enableevents = true End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. |
Application events
No Actually i don't. the two procedures i posted here are the entirety of the
code. I am runing those just to test the event trap procedure, no cells at all are changed by code. Ben -- When you lose your mind, you free your life. "Jim Thomlinson" wrote: As a guess you have a recursive call. Each time the change event is fired the procedure that runs makes an change calling the Change event to fire again. And round round it will go. You can use. Application.EnableEvents = False 'at the start of the code and Application.EnableEvents = True 'at the End of the code To turn events off while the procedure runs. -- HTH... Jim Thomlinson "ben" wrote: I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. |
Application events
yes i am well aware of how to turn off events. there is no other code
triggering as a matter of fact If resaleactive = 0 Then Exit Sub when resaleactive = 0 and the sub exits this line also will run some 40 times. when stepping it will not go through the sub declaration it will merely reset to if resaleactive = 0 then exit sub on this same subject the app_workbookopen sub will always fire twice when ONE workbook is opened. -- When you lose your mind, you free your life. "STEVE BELL" wrote: Ben, Your macro is probably triggering events that in turn call event macros.... You need to turn those events off! (I usually include this in all my subs) Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) Application.Enableevents = false If resaleactive = 0 Then Exit Sub worksheetchange wks, target Application.Enableevents = true End Sub Sub worksheetchange(wks As Worksheet, target As Range) Application.Enableevents = false MsgBox target.AddressLocal Application.Enableevents = true End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. |
Application events
Are you absolutely sure?
Suggest you take a closer look. It looks like you have more than one change event in your workbook. Both are being triggered. You need to either incorporate them into a single event macro or find out which one triggers first and stop the other from running. I played with the following change event and it required turning events off to prevent a selection change event from firing... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Dim resaleactive resaleactive = 0 If resaleactive = 0 Then Exit Sub MsgBox "Not working" Application.EnableEvents = True End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... yes i am well aware of how to turn off events. there is no other code triggering as a matter of fact If resaleactive = 0 Then Exit Sub when resaleactive = 0 and the sub exits this line also will run some 40 times. when stepping it will not go through the sub declaration it will merely reset to if resaleactive = 0 then exit sub on this same subject the app_workbookopen sub will always fire twice when ONE workbook is opened. -- When you lose your mind, you free your life. "STEVE BELL" wrote: Ben, Your macro is probably triggering events that in turn call event macros.... You need to turn those events off! (I usually include this in all my subs) Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) Application.Enableevents = false If resaleactive = 0 Then Exit Sub worksheetchange wks, target Application.Enableevents = true End Sub Sub worksheetchange(wks As Worksheet, target As Range) Application.Enableevents = false MsgBox target.AddressLocal Application.Enableevents = true End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. |
Application events
I am quite sure. The worksheet being changed has absolutely ZERO code in it.
I even added application.enableevents = false just to be 100% sure. Same problem. Again. The EVENT IS NOT RETRIGGERING. It is that once excel finishes the one line of code rathering than moving to the next it repeats itself over and over again. I have stepped through it multiple times and even tried to catch errors. Nothing errors out. Ben -- When you lose your mind, you free your life. "STEVE BELL" wrote: Are you absolutely sure? Suggest you take a closer look. It looks like you have more than one change event in your workbook. Both are being triggered. You need to either incorporate them into a single event macro or find out which one triggers first and stop the other from running. I played with the following change event and it required turning events off to prevent a selection change event from firing... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Dim resaleactive resaleactive = 0 If resaleactive = 0 Then Exit Sub MsgBox "Not working" Application.EnableEvents = True End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... yes i am well aware of how to turn off events. there is no other code triggering as a matter of fact If resaleactive = 0 Then Exit Sub when resaleactive = 0 and the sub exits this line also will run some 40 times. when stepping it will not go through the sub declaration it will merely reset to if resaleactive = 0 then exit sub on this same subject the app_workbookopen sub will always fire twice when ONE workbook is opened. -- When you lose your mind, you free your life. "STEVE BELL" wrote: Ben, Your macro is probably triggering events that in turn call event macros.... You need to turn those events off! (I usually include this in all my subs) Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) Application.Enableevents = false If resaleactive = 0 Then Exit Sub worksheetchange wks, target Application.Enableevents = true End Sub Sub worksheetchange(wks As Worksheet, target As Range) Application.Enableevents = false MsgBox target.AddressLocal Application.Enableevents = true End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. |
Application events
strike that it is retriggering, but there is no reason for it to. no other
code is anywhere! maybe casue it's in a class module catching code from a worksheet that it isn't on? -- When you lose your mind, you free your life. "ben" wrote: I am quite sure. The worksheet being changed has absolutely ZERO code in it. I even added application.enableevents = false just to be 100% sure. Same problem. Again. The EVENT IS NOT RETRIGGERING. It is that once excel finishes the one line of code rathering than moving to the next it repeats itself over and over again. I have stepped through it multiple times and even tried to catch errors. Nothing errors out. Ben -- When you lose your mind, you free your life. "STEVE BELL" wrote: Are you absolutely sure? Suggest you take a closer look. It looks like you have more than one change event in your workbook. Both are being triggered. You need to either incorporate them into a single event macro or find out which one triggers first and stop the other from running. I played with the following change event and it required turning events off to prevent a selection change event from firing... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Dim resaleactive resaleactive = 0 If resaleactive = 0 Then Exit Sub MsgBox "Not working" Application.EnableEvents = True End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... yes i am well aware of how to turn off events. there is no other code triggering as a matter of fact If resaleactive = 0 Then Exit Sub when resaleactive = 0 and the sub exits this line also will run some 40 times. when stepping it will not go through the sub declaration it will merely reset to if resaleactive = 0 then exit sub on this same subject the app_workbookopen sub will always fire twice when ONE workbook is opened. -- When you lose your mind, you free your life. "STEVE BELL" wrote: Ben, Your macro is probably triggering events that in turn call event macros.... You need to turn those events off! (I usually include this in all my subs) Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) Application.Enableevents = false If resaleactive = 0 Then Exit Sub worksheetchange wks, target Application.Enableevents = true End Sub Sub worksheetchange(wks As Worksheet, target As Range) Application.Enableevents = false MsgBox target.AddressLocal Application.Enableevents = true End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. |
Application events
ok now i don't even make sense to myself i just reread that last post. The
sub in the class module from my .xla will catch a code in another sheet. if it is the right sheet it will not retrigger the .xla code it will simply repeat the line. If it is not the right sheet it will retrigger the code. the worksheet is catching changes on has absolutely no code at all. Ben -- When you lose your mind, you free your life. "ben" wrote: strike that it is retriggering, but there is no reason for it to. no other code is anywhere! maybe casue it's in a class module catching code from a worksheet that it isn't on? -- When you lose your mind, you free your life. "ben" wrote: I am quite sure. The worksheet being changed has absolutely ZERO code in it. I even added application.enableevents = false just to be 100% sure. Same problem. Again. The EVENT IS NOT RETRIGGERING. It is that once excel finishes the one line of code rathering than moving to the next it repeats itself over and over again. I have stepped through it multiple times and even tried to catch errors. Nothing errors out. Ben -- When you lose your mind, you free your life. "STEVE BELL" wrote: Are you absolutely sure? Suggest you take a closer look. It looks like you have more than one change event in your workbook. Both are being triggered. You need to either incorporate them into a single event macro or find out which one triggers first and stop the other from running. I played with the following change event and it required turning events off to prevent a selection change event from firing... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Dim resaleactive resaleactive = 0 If resaleactive = 0 Then Exit Sub MsgBox "Not working" Application.EnableEvents = True End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... yes i am well aware of how to turn off events. there is no other code triggering as a matter of fact If resaleactive = 0 Then Exit Sub when resaleactive = 0 and the sub exits this line also will run some 40 times. when stepping it will not go through the sub declaration it will merely reset to if resaleactive = 0 then exit sub on this same subject the app_workbookopen sub will always fire twice when ONE workbook is opened. -- When you lose your mind, you free your life. "STEVE BELL" wrote: Ben, Your macro is probably triggering events that in turn call event macros.... You need to turn those events off! (I usually include this in all my subs) Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) Application.Enableevents = false If resaleactive = 0 Then Exit Sub worksheetchange wks, target Application.Enableevents = true End Sub Sub worksheetchange(wks As Worksheet, target As Range) Application.Enableevents = false MsgBox target.AddressLocal Application.Enableevents = true End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. |
Application events
this is a very confusing problem, i removed all add-ins from both excel and
vba and tested the code again, same results, i am absolutely certain no other code is triggering, just because of the stepping through, but also because no code exists and i even inserted application.enableevents = false into my code. :( frustrated to an extreme degree -- When you lose your mind, you free your life. "ben" wrote: ok now i don't even make sense to myself i just reread that last post. The sub in the class module from my .xla will catch a code in another sheet. if it is the right sheet it will not retrigger the .xla code it will simply repeat the line. If it is not the right sheet it will retrigger the code. the worksheet is catching changes on has absolutely no code at all. Ben -- When you lose your mind, you free your life. "ben" wrote: strike that it is retriggering, but there is no reason for it to. no other code is anywhere! maybe casue it's in a class module catching code from a worksheet that it isn't on? -- When you lose your mind, you free your life. "ben" wrote: I am quite sure. The worksheet being changed has absolutely ZERO code in it. I even added application.enableevents = false just to be 100% sure. Same problem. Again. The EVENT IS NOT RETRIGGERING. It is that once excel finishes the one line of code rathering than moving to the next it repeats itself over and over again. I have stepped through it multiple times and even tried to catch errors. Nothing errors out. Ben -- When you lose your mind, you free your life. "STEVE BELL" wrote: Are you absolutely sure? Suggest you take a closer look. It looks like you have more than one change event in your workbook. Both are being triggered. You need to either incorporate them into a single event macro or find out which one triggers first and stop the other from running. I played with the following change event and it required turning events off to prevent a selection change event from firing... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Dim resaleactive resaleactive = 0 If resaleactive = 0 Then Exit Sub MsgBox "Not working" Application.EnableEvents = True End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... yes i am well aware of how to turn off events. there is no other code triggering as a matter of fact If resaleactive = 0 Then Exit Sub when resaleactive = 0 and the sub exits this line also will run some 40 times. when stepping it will not go through the sub declaration it will merely reset to if resaleactive = 0 then exit sub on this same subject the app_workbookopen sub will always fire twice when ONE workbook is opened. -- When you lose your mind, you free your life. "STEVE BELL" wrote: Ben, Your macro is probably triggering events that in turn call event macros.... You need to turn those events off! (I usually include this in all my subs) Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) Application.Enableevents = false If resaleactive = 0 Then Exit Sub worksheetchange wks, target Application.Enableevents = true End Sub Sub worksheetchange(wks As Worksheet, target As Range) Application.Enableevents = false MsgBox target.AddressLocal Application.Enableevents = true End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. |
Application events
If you haven't already tried this than you need to try to see what is
happening. Place a breakpoint at the beginning of the event macro. When the code is fired Excel should take you to that point. Than just use F8 to step through and see what happens. -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... this is a very confusing problem, i removed all add-ins from both excel and vba and tested the code again, same results, i am absolutely certain no other code is triggering, just because of the stepping through, but also because no code exists and i even inserted application.enableevents = false into my code. :( frustrated to an extreme degree -- When you lose your mind, you free your life. "ben" wrote: ok now i don't even make sense to myself i just reread that last post. The sub in the class module from my .xla will catch a code in another sheet. if it is the right sheet it will not retrigger the .xla code it will simply repeat the line. If it is not the right sheet it will retrigger the code. the worksheet is catching changes on has absolutely no code at all. Ben -- When you lose your mind, you free your life. "ben" wrote: strike that it is retriggering, but there is no reason for it to. no other code is anywhere! maybe casue it's in a class module catching code from a worksheet that it isn't on? -- When you lose your mind, you free your life. "ben" wrote: I am quite sure. The worksheet being changed has absolutely ZERO code in it. I even added application.enableevents = false just to be 100% sure. Same problem. Again. The EVENT IS NOT RETRIGGERING. It is that once excel finishes the one line of code rathering than moving to the next it repeats itself over and over again. I have stepped through it multiple times and even tried to catch errors. Nothing errors out. Ben -- When you lose your mind, you free your life. "STEVE BELL" wrote: Are you absolutely sure? Suggest you take a closer look. It looks like you have more than one change event in your workbook. Both are being triggered. You need to either incorporate them into a single event macro or find out which one triggers first and stop the other from running. I played with the following change event and it required turning events off to prevent a selection change event from firing... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Dim resaleactive resaleactive = 0 If resaleactive = 0 Then Exit Sub MsgBox "Not working" Application.EnableEvents = True End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... yes i am well aware of how to turn off events. there is no other code triggering as a matter of fact If resaleactive = 0 Then Exit Sub when resaleactive = 0 and the sub exits this line also will run some 40 times. when stepping it will not go through the sub declaration it will merely reset to if resaleactive = 0 then exit sub on this same subject the app_workbookopen sub will always fire twice when ONE workbook is opened. -- When you lose your mind, you free your life. "STEVE BELL" wrote: Ben, Your macro is probably triggering events that in turn call event macros.... You need to turn those events off! (I usually include this in all my subs) Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) Application.Enableevents = false If resaleactive = 0 Then Exit Sub worksheetchange wks, target Application.Enableevents = true End Sub Sub worksheetchange(wks As Worksheet, target As Range) Application.Enableevents = false MsgBox target.AddressLocal Application.Enableevents = true End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. |
Application events
steve maybe i didn't phrase it right but yes i have tried that i have
"stepped" through the code multiple times that is how i know excel is repeating the same command without moving to the next command. for instance if resaleactive = 0 then exit sub when i step through and resaleactive = 0 then it will read the exit sub command but will loop back to checking the vallue of resaleactive, it will loop exactly 42 times. then quit unexpectedly without finishing the rest of the commands. Ben -- When you lose your mind, you free your life. "STEVE BELL" wrote: If you haven't already tried this than you need to try to see what is happening. Place a breakpoint at the beginning of the event macro. When the code is fired Excel should take you to that point. Than just use F8 to step through and see what happens. -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... this is a very confusing problem, i removed all add-ins from both excel and vba and tested the code again, same results, i am absolutely certain no other code is triggering, just because of the stepping through, but also because no code exists and i even inserted application.enableevents = false into my code. :( frustrated to an extreme degree -- When you lose your mind, you free your life. "ben" wrote: ok now i don't even make sense to myself i just reread that last post. The sub in the class module from my .xla will catch a code in another sheet. if it is the right sheet it will not retrigger the .xla code it will simply repeat the line. If it is not the right sheet it will retrigger the code. the worksheet is catching changes on has absolutely no code at all. Ben -- When you lose your mind, you free your life. "ben" wrote: strike that it is retriggering, but there is no reason for it to. no other code is anywhere! maybe casue it's in a class module catching code from a worksheet that it isn't on? -- When you lose your mind, you free your life. "ben" wrote: I am quite sure. The worksheet being changed has absolutely ZERO code in it. I even added application.enableevents = false just to be 100% sure. Same problem. Again. The EVENT IS NOT RETRIGGERING. It is that once excel finishes the one line of code rathering than moving to the next it repeats itself over and over again. I have stepped through it multiple times and even tried to catch errors. Nothing errors out. Ben -- When you lose your mind, you free your life. "STEVE BELL" wrote: Are you absolutely sure? Suggest you take a closer look. It looks like you have more than one change event in your workbook. Both are being triggered. You need to either incorporate them into a single event macro or find out which one triggers first and stop the other from running. I played with the following change event and it required turning events off to prevent a selection change event from firing... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Dim resaleactive resaleactive = 0 If resaleactive = 0 Then Exit Sub MsgBox "Not working" Application.EnableEvents = True End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... yes i am well aware of how to turn off events. there is no other code triggering as a matter of fact If resaleactive = 0 Then Exit Sub when resaleactive = 0 and the sub exits this line also will run some 40 times. when stepping it will not go through the sub declaration it will merely reset to if resaleactive = 0 then exit sub on this same subject the app_workbookopen sub will always fire twice when ONE workbook is opened. -- When you lose your mind, you free your life. "STEVE BELL" wrote: Ben, Your macro is probably triggering events that in turn call event macros.... You need to turn those events off! (I usually include this in all my subs) Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) Application.Enableevents = false If resaleactive = 0 Then Exit Sub worksheetchange wks, target Application.Enableevents = true End Sub Sub worksheetchange(wks As Worksheet, target As Range) Application.Enableevents = false MsgBox target.AddressLocal Application.Enableevents = true End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. |
Application events
No help here, but I put this in the ThisWorkbook module:
Option Explicit Public WithEvents xlApp As Excel.Application Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub Workbook_Close() Set xlApp = Nothing End Sub Private Sub xlApp_SheetChange(ByVal wks As Object, ByVal target As Range) Dim resaleactive As Long resaleactive = 33 If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub And this in a general module: Option Explicit Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub And the only way I could see the problem you had was to have sheets grouped together and then making a change to one of the grouped worksheets. (It cycled through each of the grouped sheets.) I used xl2003 in my test. Is there any chance that the sheets were grouped??? ben wrote: I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. -- Dave Peterson |
Application events
Ben,
Can you send me all the code and let me play with it? Do you trust me to work with a copy of your workbook? (you can send it to my email) But be patient (I'm going out for a few hours)... -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... steve maybe i didn't phrase it right but yes i have tried that i have "stepped" through the code multiple times that is how i know excel is repeating the same command without moving to the next command. for instance if resaleactive = 0 then exit sub when i step through and resaleactive = 0 then it will read the exit sub command but will loop back to checking the vallue of resaleactive, it will loop exactly 42 times. then quit unexpectedly without finishing the rest of the commands. Ben -- When you lose your mind, you free your life. "STEVE BELL" wrote: If you haven't already tried this than you need to try to see what is happening. Place a breakpoint at the beginning of the event macro. When the code is fired Excel should take you to that point. Than just use F8 to step through and see what happens. -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... this is a very confusing problem, i removed all add-ins from both excel and vba and tested the code again, same results, i am absolutely certain no other code is triggering, just because of the stepping through, but also because no code exists and i even inserted application.enableevents = false into my code. :( frustrated to an extreme degree -- When you lose your mind, you free your life. "ben" wrote: ok now i don't even make sense to myself i just reread that last post. The sub in the class module from my .xla will catch a code in another sheet. if it is the right sheet it will not retrigger the .xla code it will simply repeat the line. If it is not the right sheet it will retrigger the code. the worksheet is catching changes on has absolutely no code at all. Ben -- When you lose your mind, you free your life. "ben" wrote: strike that it is retriggering, but there is no reason for it to. no other code is anywhere! maybe casue it's in a class module catching code from a worksheet that it isn't on? -- When you lose your mind, you free your life. "ben" wrote: I am quite sure. The worksheet being changed has absolutely ZERO code in it. I even added application.enableevents = false just to be 100% sure. Same problem. Again. The EVENT IS NOT RETRIGGERING. It is that once excel finishes the one line of code rathering than moving to the next it repeats itself over and over again. I have stepped through it multiple times and even tried to catch errors. Nothing errors out. Ben -- When you lose your mind, you free your life. "STEVE BELL" wrote: Are you absolutely sure? Suggest you take a closer look. It looks like you have more than one change event in your workbook. Both are being triggered. You need to either incorporate them into a single event macro or find out which one triggers first and stop the other from running. I played with the following change event and it required turning events off to prevent a selection change event from firing... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Dim resaleactive resaleactive = 0 If resaleactive = 0 Then Exit Sub MsgBox "Not working" Application.EnableEvents = True End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... yes i am well aware of how to turn off events. there is no other code triggering as a matter of fact If resaleactive = 0 Then Exit Sub when resaleactive = 0 and the sub exits this line also will run some 40 times. when stepping it will not go through the sub declaration it will merely reset to if resaleactive = 0 then exit sub on this same subject the app_workbookopen sub will always fire twice when ONE workbook is opened. -- When you lose your mind, you free your life. "STEVE BELL" wrote: Ben, Your macro is probably triggering events that in turn call event macros.... You need to turn those events off! (I usually include this in all my subs) Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) Application.Enableevents = false If resaleactive = 0 Then Exit Sub worksheetchange wks, target Application.Enableevents = true End Sub Sub worksheetchange(wks As Worksheet, target As Range) Application.Enableevents = false MsgBox target.AddressLocal Application.Enableevents = true End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. |
Application events
P.S.
Can't promise to find the problem. But I can give it a try... -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... steve maybe i didn't phrase it right but yes i have tried that i have "stepped" through the code multiple times that is how i know excel is repeating the same command without moving to the next command. for instance if resaleactive = 0 then exit sub when i step through and resaleactive = 0 then it will read the exit sub command but will loop back to checking the vallue of resaleactive, it will loop exactly 42 times. then quit unexpectedly without finishing the rest of the commands. Ben -- When you lose your mind, you free your life. "STEVE BELL" wrote: If you haven't already tried this than you need to try to see what is happening. Place a breakpoint at the beginning of the event macro. When the code is fired Excel should take you to that point. Than just use F8 to step through and see what happens. -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... this is a very confusing problem, i removed all add-ins from both excel and vba and tested the code again, same results, i am absolutely certain no other code is triggering, just because of the stepping through, but also because no code exists and i even inserted application.enableevents = false into my code. :( frustrated to an extreme degree -- When you lose your mind, you free your life. "ben" wrote: ok now i don't even make sense to myself i just reread that last post. The sub in the class module from my .xla will catch a code in another sheet. if it is the right sheet it will not retrigger the .xla code it will simply repeat the line. If it is not the right sheet it will retrigger the code. the worksheet is catching changes on has absolutely no code at all. Ben -- When you lose your mind, you free your life. "ben" wrote: strike that it is retriggering, but there is no reason for it to. no other code is anywhere! maybe casue it's in a class module catching code from a worksheet that it isn't on? -- When you lose your mind, you free your life. "ben" wrote: I am quite sure. The worksheet being changed has absolutely ZERO code in it. I even added application.enableevents = false just to be 100% sure. Same problem. Again. The EVENT IS NOT RETRIGGERING. It is that once excel finishes the one line of code rathering than moving to the next it repeats itself over and over again. I have stepped through it multiple times and even tried to catch errors. Nothing errors out. Ben -- When you lose your mind, you free your life. "STEVE BELL" wrote: Are you absolutely sure? Suggest you take a closer look. It looks like you have more than one change event in your workbook. Both are being triggered. You need to either incorporate them into a single event macro or find out which one triggers first and stop the other from running. I played with the following change event and it required turning events off to prevent a selection change event from firing... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Dim resaleactive resaleactive = 0 If resaleactive = 0 Then Exit Sub MsgBox "Not working" Application.EnableEvents = True End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... yes i am well aware of how to turn off events. there is no other code triggering as a matter of fact If resaleactive = 0 Then Exit Sub when resaleactive = 0 and the sub exits this line also will run some 40 times. when stepping it will not go through the sub declaration it will merely reset to if resaleactive = 0 then exit sub on this same subject the app_workbookopen sub will always fire twice when ONE workbook is opened. -- When you lose your mind, you free your life. "STEVE BELL" wrote: Ben, Your macro is probably triggering events that in turn call event macros.... You need to turn those events off! (I usually include this in all my subs) Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) Application.Enableevents = false If resaleactive = 0 Then Exit Sub worksheetchange wks, target Application.Enableevents = true End Sub Sub worksheetchange(wks As Worksheet, target As Range) Application.Enableevents = false MsgBox target.AddressLocal Application.Enableevents = true End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. |
Application events
hmmmm i don't know about that, i only have one worksheet in the workbook that
was being caught. is there a change it would do so if they were group to another workbook? (as far as i know i ahve no sheets grouped) Ben -- When you lose your mind, you free your life. "Dave Peterson" wrote: No help here, but I put this in the ThisWorkbook module: Option Explicit Public WithEvents xlApp As Excel.Application Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub Workbook_Close() Set xlApp = Nothing End Sub Private Sub xlApp_SheetChange(ByVal wks As Object, ByVal target As Range) Dim resaleactive As Long resaleactive = 33 If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub And this in a general module: Option Explicit Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub And the only way I could see the problem you had was to have sheets grouped together and then making a change to one of the grouped worksheets. (It cycled through each of the grouped sheets.) I used xl2003 in my test. Is there any chance that the sheets were grouped??? ben wrote: I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. -- Dave Peterson |
Application events
it's an addin and blank template, warning - the template does read and write
your registry with savesetting and getsetting, waht's your email and i'll send them Ben -- When you lose your mind, you free your life. "STEVE BELL" wrote: P.S. Can't promise to find the problem. But I can give it a try... -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... steve maybe i didn't phrase it right but yes i have tried that i have "stepped" through the code multiple times that is how i know excel is repeating the same command without moving to the next command. for instance if resaleactive = 0 then exit sub when i step through and resaleactive = 0 then it will read the exit sub command but will loop back to checking the vallue of resaleactive, it will loop exactly 42 times. then quit unexpectedly without finishing the rest of the commands. Ben -- When you lose your mind, you free your life. "STEVE BELL" wrote: If you haven't already tried this than you need to try to see what is happening. Place a breakpoint at the beginning of the event macro. When the code is fired Excel should take you to that point. Than just use F8 to step through and see what happens. -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... this is a very confusing problem, i removed all add-ins from both excel and vba and tested the code again, same results, i am absolutely certain no other code is triggering, just because of the stepping through, but also because no code exists and i even inserted application.enableevents = false into my code. :( frustrated to an extreme degree -- When you lose your mind, you free your life. "ben" wrote: ok now i don't even make sense to myself i just reread that last post. The sub in the class module from my .xla will catch a code in another sheet. if it is the right sheet it will not retrigger the .xla code it will simply repeat the line. If it is not the right sheet it will retrigger the code. the worksheet is catching changes on has absolutely no code at all. Ben -- When you lose your mind, you free your life. "ben" wrote: strike that it is retriggering, but there is no reason for it to. no other code is anywhere! maybe casue it's in a class module catching code from a worksheet that it isn't on? -- When you lose your mind, you free your life. "ben" wrote: I am quite sure. The worksheet being changed has absolutely ZERO code in it. I even added application.enableevents = false just to be 100% sure. Same problem. Again. The EVENT IS NOT RETRIGGERING. It is that once excel finishes the one line of code rathering than moving to the next it repeats itself over and over again. I have stepped through it multiple times and even tried to catch errors. Nothing errors out. Ben -- When you lose your mind, you free your life. "STEVE BELL" wrote: Are you absolutely sure? Suggest you take a closer look. It looks like you have more than one change event in your workbook. Both are being triggered. You need to either incorporate them into a single event macro or find out which one triggers first and stop the other from running. I played with the following change event and it required turning events off to prevent a selection change event from firing... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Dim resaleactive resaleactive = 0 If resaleactive = 0 Then Exit Sub MsgBox "Not working" Application.EnableEvents = True End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... yes i am well aware of how to turn off events. there is no other code triggering as a matter of fact If resaleactive = 0 Then Exit Sub when resaleactive = 0 and the sub exits this line also will run some 40 times. when stepping it will not go through the sub declaration it will merely reset to if resaleactive = 0 then exit sub on this same subject the app_workbookopen sub will always fire twice when ONE workbook is opened. -- When you lose your mind, you free your life. "STEVE BELL" wrote: Ben, Your macro is probably triggering events that in turn call event macros.... You need to turn those events off! (I usually include this in all my subs) Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) Application.Enableevents = false If resaleactive = 0 Then Exit Sub worksheetchange wks, target Application.Enableevents = true End Sub Sub worksheetchange(wks As Worksheet, target As Range) Application.Enableevents = false MsgBox target.AddressLocal Application.Enableevents = true End Sub -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. |
Application events
Grouping the worksheets is what you do when you click on a worksheet tab and
ctrl-click on subsequent. The worksheet tabs will change color--like they're all active. If you only have one worksheet in that workbook that's changing, then this wasn't the cause. But in your code, you used resaleactive. But I didn't see where that was picked up. Anything funny happening in that area of your code??? ben wrote: hmmmm i don't know about that, i only have one worksheet in the workbook that was being caught. is there a change it would do so if they were group to another workbook? (as far as i know i ahve no sheets grouped) Ben -- When you lose your mind, you free your life. "Dave Peterson" wrote: No help here, but I put this in the ThisWorkbook module: Option Explicit Public WithEvents xlApp As Excel.Application Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub Workbook_Close() Set xlApp = Nothing End Sub Private Sub xlApp_SheetChange(ByVal wks As Object, ByVal target As Range) Dim resaleactive As Long resaleactive = 33 If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub And this in a general module: Option Explicit Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub And the only way I could see the problem you had was to have sheets grouped together and then making a change to one of the grouped worksheets. (It cycled through each of the grouped sheets.) I used xl2003 in my test. Is there any chance that the sheets were grouped??? ben wrote: I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. -- Dave Peterson -- Dave Peterson |
Application events
Ps. You may want to try the basic functionality in a brand new workbook. Post
back with your results, you code, and the version of excel. Maybe a volunteer with the same version can jump in. ben wrote: hmmmm i don't know about that, i only have one worksheet in the workbook that was being caught. is there a change it would do so if they were group to another workbook? (as far as i know i ahve no sheets grouped) Ben -- When you lose your mind, you free your life. "Dave Peterson" wrote: No help here, but I put this in the ThisWorkbook module: Option Explicit Public WithEvents xlApp As Excel.Application Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub Workbook_Close() Set xlApp = Nothing End Sub Private Sub xlApp_SheetChange(ByVal wks As Object, ByVal target As Range) Dim resaleactive As Long resaleactive = 33 If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub And this in a general module: Option Explicit Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub And the only way I could see the problem you had was to have sheets grouped together and then making a change to one of the grouped worksheets. (It cycled through each of the grouped sheets.) I used xl2003 in my test. Is there any chance that the sheets were grouped??? ben wrote: I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. -- Dave Peterson -- Dave Peterson |
Application events
resaleactive is a public variable in module1 where the worksheetchange sub is
at the app_sheetchange sub is in the class module xl2003 sp1 win xp sp2 -- When you lose your mind, you free your life. "Dave Peterson" wrote: Ps. You may want to try the basic functionality in a brand new workbook. Post back with your results, you code, and the version of excel. Maybe a volunteer with the same version can jump in. ben wrote: hmmmm i don't know about that, i only have one worksheet in the workbook that was being caught. is there a change it would do so if they were group to another workbook? (as far as i know i ahve no sheets grouped) Ben -- When you lose your mind, you free your life. "Dave Peterson" wrote: No help here, but I put this in the ThisWorkbook module: Option Explicit Public WithEvents xlApp As Excel.Application Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub Workbook_Close() Set xlApp = Nothing End Sub Private Sub xlApp_SheetChange(ByVal wks As Object, ByVal target As Range) Dim resaleactive As Long resaleactive = 33 If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub And this in a general module: Option Explicit Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub And the only way I could see the problem you had was to have sheets grouped together and then making a change to one of the grouped worksheets. (It cycled through each of the grouped sheets.) I used xl2003 in my test. Is there any chance that the sheets were grouped??? ben wrote: I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. -- Dave Peterson -- Dave Peterson |
Application events
What happened when you skinnied the code down to just its basics?
ben wrote: resaleactive is a public variable in module1 where the worksheetchange sub is at the app_sheetchange sub is in the class module xl2003 sp1 win xp sp2 -- When you lose your mind, you free your life. "Dave Peterson" wrote: Ps. You may want to try the basic functionality in a brand new workbook. Post back with your results, you code, and the version of excel. Maybe a volunteer with the same version can jump in. ben wrote: hmmmm i don't know about that, i only have one worksheet in the workbook that was being caught. is there a change it would do so if they were group to another workbook? (as far as i know i ahve no sheets grouped) Ben -- When you lose your mind, you free your life. "Dave Peterson" wrote: No help here, but I put this in the ThisWorkbook module: Option Explicit Public WithEvents xlApp As Excel.Application Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub Workbook_Close() Set xlApp = Nothing End Sub Private Sub xlApp_SheetChange(ByVal wks As Object, ByVal target As Range) Dim resaleactive As Long resaleactive = 33 If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub And this in a general module: Option Explicit Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub And the only way I could see the problem you had was to have sheets grouped together and then making a change to one of the grouped worksheets. (It cycled through each of the grouped sheets.) I used xl2003 in my test. Is there any chance that the sheets were grouped??? ben wrote: I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Application events
Ben,
Now that Dave has joined the discussion - you have a source of Excellent help. He is far beyond anything I could do to help. Also - you are using 2003 and I only have 2000. Stick with Dave... -- steveB Remove "AYN" from email to respond "ben" (remove this if mailing direct) wrote in message ... hmmmm i don't know about that, i only have one worksheet in the workbook that was being caught. is there a change it would do so if they were group to another workbook? (as far as i know i ahve no sheets grouped) Ben -- When you lose your mind, you free your life. "Dave Peterson" wrote: No help here, but I put this in the ThisWorkbook module: Option Explicit Public WithEvents xlApp As Excel.Application Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub Workbook_Close() Set xlApp = Nothing End Sub Private Sub xlApp_SheetChange(ByVal wks As Object, ByVal target As Range) Dim resaleactive As Long resaleactive = 33 If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub And this in a general module: Option Explicit Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub And the only way I could see the problem you had was to have sheets grouped together and then making a change to one of the grouped worksheets. (It cycled through each of the grouped sheets.) I used xl2003 in my test. Is there any chance that the sheets were grouped??? ben wrote: I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. -- Dave Peterson |
Application events
Hi,
there might be many instances of the class possibly. it could be checked by the following. if all addresses are the same, there is one instance. 'your class module 'Add this line at the top of the module Private cnt As Long Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target 'Add this line cnt = cnt + 1 Debug.Print "variable address:", ObjPtr(Me), cnt End Sub -- HTH okaizawa ben wrote: I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? |
Application events
When i skinned it to just the class code to catch change events and transfer
code to a regular module (with only that one function) in the module it stopped firing multiple times, i don't have enough experience to know if maybe i have to class instanced multiple times???? Ben -- When you lose your mind, you free your life. "Dave Peterson" wrote: What happened when you skinnied the code down to just its basics? ben wrote: resaleactive is a public variable in module1 where the worksheetchange sub is at the app_sheetchange sub is in the class module xl2003 sp1 win xp sp2 -- When you lose your mind, you free your life. "Dave Peterson" wrote: Ps. You may want to try the basic functionality in a brand new workbook. Post back with your results, you code, and the version of excel. Maybe a volunteer with the same version can jump in. ben wrote: hmmmm i don't know about that, i only have one worksheet in the workbook that was being caught. is there a change it would do so if they were group to another workbook? (as far as i know i ahve no sheets grouped) Ben -- When you lose your mind, you free your life. "Dave Peterson" wrote: No help here, but I put this in the ThisWorkbook module: Option Explicit Public WithEvents xlApp As Excel.Application Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub Workbook_Close() Set xlApp = Nothing End Sub Private Sub xlApp_SheetChange(ByVal wks As Object, ByVal target As Range) Dim resaleactive As Long resaleactive = 33 If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub And this in a general module: Option Explicit Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub And the only way I could see the problem you had was to have sheets grouped together and then making a change to one of the grouped worksheets. (It cycled through each of the grouped sheets.) I used xl2003 in my test. Is there any chance that the sheets were grouped??? ben wrote: I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Application events
hmm interesting
cnt = 6 and the address changes everytime how do i go about correcting that? This add-in is actually going to be very class intensive. Ben -- When you lose your mind, you free your life. "okaizawa" wrote: Hi, there might be many instances of the class possibly. it could be checked by the following. if all addresses are the same, there is one instance. 'your class module 'Add this line at the top of the module Private cnt As Long Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target 'Add this line cnt = cnt + 1 Debug.Print "variable address:", ObjPtr(Me), cnt End Sub -- HTH okaizawa ben wrote: I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? |
Application events
I think I'd look at the code that was cut out and see if you have anything
making a change. ben wrote: When i skinned it to just the class code to catch change events and transfer code to a regular module (with only that one function) in the module it stopped firing multiple times, i don't have enough experience to know if maybe i have to class instanced multiple times???? Ben -- When you lose your mind, you free your life. "Dave Peterson" wrote: What happened when you skinnied the code down to just its basics? ben wrote: resaleactive is a public variable in module1 where the worksheetchange sub is at the app_sheetchange sub is in the class module xl2003 sp1 win xp sp2 -- When you lose your mind, you free your life. "Dave Peterson" wrote: Ps. You may want to try the basic functionality in a brand new workbook. Post back with your results, you code, and the version of excel. Maybe a volunteer with the same version can jump in. ben wrote: hmmmm i don't know about that, i only have one worksheet in the workbook that was being caught. is there a change it would do so if they were group to another workbook? (as far as i know i ahve no sheets grouped) Ben -- When you lose your mind, you free your life. "Dave Peterson" wrote: No help here, but I put this in the ThisWorkbook module: Option Explicit Public WithEvents xlApp As Excel.Application Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub Workbook_Close() Set xlApp = Nothing End Sub Private Sub xlApp_SheetChange(ByVal wks As Object, ByVal target As Range) Dim resaleactive As Long resaleactive = 33 If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub And this in a general module: Option Explicit Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub And the only way I could see the problem you had was to have sheets grouped together and then making a change to one of the grouped worksheets. (It cycled through each of the grouped sheets.) I used xl2003 in my test. Is there any chance that the sheets were grouped??? ben wrote: I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Application events
i believe i may know what's going wrong but not sure how to correct it. I
have on the worksheet that is to be used, many embedded controls, rather that right code for all of them onto that worksheet i use my .xla to class all of them into groups (checkboxes,command buttons, labels, spin buttons, etc...). they class when the worksheet is loaded, or activated, etc... when i remove that classing sub then they event triggers only once, i have a feeling my coding for that is causeing more than one instance of my class to appear, Sub classbuttons() Dim clsCBEvents As Class1 Dim lblbuttons As Class1 Dim sbbuttons As Class1 Dim sbuttons As Class1 Dim shp As Shape Set mcolEvents = New Collection Set sbuttonevents = New Collection Set lblevents = New Collection Set sbevents = New Collection For Each shp In ActiveSheet.Shapes If shp.Type = msoOLEControlObject Then If TypeOf shp.OLEFormat.Object.Object Is MSForms.CheckBox Then Set clsCBEvents = New Class1 Set clsCBEvents.cbGroup = shp.OLEFormat.Object.Object mcolEvents.Add clsCBEvents End If If TypeOf shp.OLEFormat.Object.Object Is CommandButton Then Set sbuttons = New Class1 Set sbuttons.comGroup = shp.OLEFormat.Object.Object sbuttonevents.Add sbuttons End If If TypeOf shp.OLEFormat.Object.Object Is MSForms.Label Then Set lblbuttons = New Class1 Set lblbuttons.lblGroup = shp.OLEFormat.Object.Object lblevents.Add lblbuttons End If If TypeOf shp.OLEFormat.Object.Object Is MSForms.SpinButton Then Set sbbuttons = New Class1 Set sbbuttons.sbGroup = shp.OLEFormat.Object.Object sbevents.Add sbbuttons End If End If Next End Sub can i change it anyway so that instead of creating a new instance over and over again it uses the same class that is existing and maybe creating a seperate class module for the controls would help??? -- When you lose your mind, you free your life. "ben" wrote: hmm interesting cnt = 6 and the address changes everytime how do i go about correcting that? This add-in is actually going to be very class intensive. Ben -- When you lose your mind, you free your life. "okaizawa" wrote: Hi, there might be many instances of the class possibly. it could be checked by the following. if all addresses are the same, there is one instance. 'your class module 'Add this line at the top of the module Private cnt As Long Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target 'Add this line cnt = cnt + 1 Debug.Print "variable address:", ObjPtr(Me), cnt End Sub -- HTH okaizawa ben wrote: I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? |
Application events
ok i have moved all classing of buttons to a new class module and the event
code only triggers once, however now i can not get the button code to fire right, i have never been able to get two classes working in one project, i must be missing something. Ben -- When you lose your mind, you free your life. "ben" wrote: i believe i may know what's going wrong but not sure how to correct it. I have on the worksheet that is to be used, many embedded controls, rather that right code for all of them onto that worksheet i use my .xla to class all of them into groups (checkboxes,command buttons, labels, spin buttons, etc...). they class when the worksheet is loaded, or activated, etc... when i remove that classing sub then they event triggers only once, i have a feeling my coding for that is causeing more than one instance of my class to appear, Sub classbuttons() Dim clsCBEvents As Class1 Dim lblbuttons As Class1 Dim sbbuttons As Class1 Dim sbuttons As Class1 Dim shp As Shape Set mcolEvents = New Collection Set sbuttonevents = New Collection Set lblevents = New Collection Set sbevents = New Collection For Each shp In ActiveSheet.Shapes If shp.Type = msoOLEControlObject Then If TypeOf shp.OLEFormat.Object.Object Is MSForms.CheckBox Then Set clsCBEvents = New Class1 Set clsCBEvents.cbGroup = shp.OLEFormat.Object.Object mcolEvents.Add clsCBEvents End If If TypeOf shp.OLEFormat.Object.Object Is CommandButton Then Set sbuttons = New Class1 Set sbuttons.comGroup = shp.OLEFormat.Object.Object sbuttonevents.Add sbuttons End If If TypeOf shp.OLEFormat.Object.Object Is MSForms.Label Then Set lblbuttons = New Class1 Set lblbuttons.lblGroup = shp.OLEFormat.Object.Object lblevents.Add lblbuttons End If If TypeOf shp.OLEFormat.Object.Object Is MSForms.SpinButton Then Set sbbuttons = New Class1 Set sbbuttons.sbGroup = shp.OLEFormat.Object.Object sbevents.Add sbbuttons End If End If Next End Sub can i change it anyway so that instead of creating a new instance over and over again it uses the same class that is existing and maybe creating a seperate class module for the controls would help??? -- When you lose your mind, you free your life. "ben" wrote: hmm interesting cnt = 6 and the address changes everytime how do i go about correcting that? This add-in is actually going to be very class intensive. Ben -- When you lose your mind, you free your life. "okaizawa" wrote: Hi, there might be many instances of the class possibly. it could be checked by the following. if all addresses are the same, there is one instance. 'your class module 'Add this line at the top of the module Private cnt As Long Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target 'Add this line cnt = cnt + 1 Debug.Print "variable address:", ObjPtr(Me), cnt End Sub -- HTH okaizawa ben wrote: I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? |
Application events
okaizawa was right i had multiple instances of the class. I have other
replies to him in this thread that explain it. Ben -- When you lose your mind, you free your life. "Dave Peterson" wrote: I think I'd look at the code that was cut out and see if you have anything making a change. ben wrote: When i skinned it to just the class code to catch change events and transfer code to a regular module (with only that one function) in the module it stopped firing multiple times, i don't have enough experience to know if maybe i have to class instanced multiple times???? Ben -- When you lose your mind, you free your life. "Dave Peterson" wrote: What happened when you skinnied the code down to just its basics? ben wrote: resaleactive is a public variable in module1 where the worksheetchange sub is at the app_sheetchange sub is in the class module xl2003 sp1 win xp sp2 -- When you lose your mind, you free your life. "Dave Peterson" wrote: Ps. You may want to try the basic functionality in a brand new workbook. Post back with your results, you code, and the version of excel. Maybe a volunteer with the same version can jump in. ben wrote: hmmmm i don't know about that, i only have one worksheet in the workbook that was being caught. is there a change it would do so if they were group to another workbook? (as far as i know i ahve no sheets grouped) Ben -- When you lose your mind, you free your life. "Dave Peterson" wrote: No help here, but I put this in the ThisWorkbook module: Option Explicit Public WithEvents xlApp As Excel.Application Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub Workbook_Close() Set xlApp = Nothing End Sub Private Sub xlApp_SheetChange(ByVal wks As Object, ByVal target As Range) Dim resaleactive As Long resaleactive = 33 If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub And this in a general module: Option Explicit Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub And the only way I could see the problem you had was to have sheets grouped together and then making a change to one of the grouped worksheets. (It cycled through each of the grouped sheets.) I used xl2003 in my test. Is there any chance that the sheets were grouped??? ben wrote: I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? -- When you lose your mind, you free your life. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Application events
okaizawa,
Thank you very much, once i identified that i moved other classing to a seperate class module and that solved everything!!! thanks much Ben -- When you lose your mind, you free your life. "okaizawa" wrote: Hi, there might be many instances of the class possibly. it could be checked by the following. if all addresses are the same, there is one instance. 'your class module 'Add this line at the top of the module Private cnt As Long Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target 'Add this line cnt = cnt + 1 Debug.Print "variable address:", ObjPtr(Me), cnt End Sub -- HTH okaizawa ben wrote: I have an App_sheetchange sub that catches changes to all worksheets and decides which sub to call based on which sheet, it catches the changes and evaluates all right, only problem is it repeats itself multiple times (43 to be exact) on one little sheet change. Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range) If resaleactive = 0 Then Exit Sub worksheetchange wks, target End Sub Sub worksheetchange(wks As Worksheet, target As Range) MsgBox target.AddressLocal End Sub for some reason when stepping through after the end sub command for worksheetchange it will loop right back the command that called it and repeat that for quite a while then just end inexplicably any thoughts on why? |
| All times are GMT +1. The time now is 01:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com