If you have the code

application.EnableEvents = False

then that would account for events not working if you encounter an error
after that line but before your code does

Application.EnableEvents = True

At the top of your event you could do

On Error Goto ErrHandler

then as the last part of the macro

ErrHandler:
Application.enableEvents = True
End Sub

Don't put any code above ErrHandler: to jump over it - that way, any
trappable error will cause events to be enabled.

You can go to the immediate window and type

Application.EnableEvents = True <Cr

to reset it is for some reason it gets set to false during development.


A dde change does not trigger the change event in Excel 97, but should in
later versions.

You can also use SetLinkOnData to handle changes by DDE.

In the macro you designate to run, you can use application.Caller to return
a reference to the changed cell.

I can't explain what is going on with the multiple projects - never
encountered that.

Regards,
Tom Ogilvy

Ross wrote in message
m...
hi there,

I have implemented a worksheet_change macro as suggested by someone in
this group. I am having some rather odd problems at this point which
I hope you can help with.

Sometimes the worksheet_change macro seems to run and sometimes it
doesn't. dn particular, after any sort of error, it doesn't run
again, sometimes even if I close and restart the workbook. I have to
actually close and restart Excel entirely for it to work.

The cells which will be changing will eventually come from DDE links.
Does this mean I have to use worksheet_calculate instead of
worksheet_change? if so, how would that work, if I still need to know
what cell changed and so some work with other cells on the same row
when it changes, i.e. generating messages if certain comparison tests
are true?

Also, when I do alt-F11, after an error, I see two VBA projects of the
same name for my spreadsheet! And if I change one the change is not
reflected in the other.

Finally, if I get an error, sometimes it opens the VBA code for a
prior version of my spreadsheet (which has a slightly different name.)

The whole thing is driving me pretty crazy! Any help would be much
appreciated!

Thanks
Ross