Macro loop not terminating

[sachinahj]

Hi i have a macro that is supposed to execute everytime something is changed on a sheet other than the summary one. the code is as follows

Please Login or Register  to view this content.

The problem is that the code will run and do what its supposed to. But then it won't stop and i have to break it to to unfreeze my excel program. any help?

[vandan_tanna]

you are changing worksheet during worksheet change event!

my untested revision:

Please Login or Register  to view this content.

Note: I added enableevents = false at the beginning and True at the end. Also addded ErrorHandler to ensure enableevents is always set back to true.

[Marcol]

@ vandan_tanna
You will need
Application.EnableEvents = True in your ErrorHandler

Please Login or Register  to view this content.

This isn't required

Please Login or Register  to view this content.

[JosephP]

Marcol
if you don't have the errorexit part you'll get an error message every time

[vandan_tanna]

@Marcol:

I like single point exit. This is why in ErrorHandler I just show the error message and then go to ErrorExit where I reset the EnableEvents and exit the sub. For this particular example, only one item (EnableEvents) has to be reset, but in some cases a lot more needs to be reset. With my method, there is just one place - ErrorExit - where you reset everything, and you get to ErrorExit regardless of error! I guess is a matter of preference.

You solution will work also as long as you add exit sub before ErrorHandler (as JosephP pointed out)

[sachinahj]

Do i have to have an error handle?? I just started VBA this week and Id prefer not to put a debug and error handle into this simple code.

What if i change the sheetchange to sheetcalculate.. when i do this the macro doesn't execute when i hit cmd + =.

Any suggestions... Id rather have it on sheet calculate anyway but it wasn't working in the first place so thats why i gave up on that.

[JosephP]

why would you want to not handle errors? you can simplify to

Please Login or Register  to view this content.

[Marcol]

Apologies, a bit slap dash there ...
Another way would be

Please Login or Register  to view this content.