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.

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.

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.

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.

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.

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.

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.

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.

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

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

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

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

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

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

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

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?

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?

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?

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?

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?