Application events

[Guest]

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?

 

[Guest]

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.

 

[STEVE BELL]

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

 

[Guest]

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

 

[Guest]

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.

 

[STEVE BELL]

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

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

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

 

[STEVE BELL]

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.

 

[Guest]

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

 

[Dave Peterson]

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???

 

[STEVE BELL]

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)...

 

[STEVE BELL]

P.S.

Can't promise to find the problem. But I can give it a try...

 

[Guest]

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

 

[Guest]

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

 

[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???

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

 

[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.

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

 

[Guest]

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