[Tom Ogilvy]

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

[Tom Ogilvy]

=if(and(A1B1,C1D1),Macro22(100,"ABD"),F1)

if you have a function in a general module such as:

Public function Macro22(a,b)
Dim rng as Range
set rng = Application.Caller
' other code


Macro22 = "Logged"
End Function

rng will hold a reference to the cell containing the formula that triggered
the function

I believe it can append to a textfile - never tried it from a UDF.

See reference for information on Appending to a file
http://support.microsoft.com/support...eio/fileio.asp
File Access with Visual Basic® for Applications


Look at the help on SetLinkOnData

It says more than I could say.

--
Regards,
Tom Ogilvy



Ross wrote in message
om...
Hi Tom,

Could you please explain a bit more about:

"You can also use SetLinkOnData to handle changes by DDE."

and

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

???

Also, can you tell me whether it's possible to pass parameters to a
macro from a cell, i.e. to have a cell which does something like:
If cell othercell and thirdcellfourthcell then run "macro22" and
give it values of cell and othercell, plus the value of one more label
cell. Then macro 22 would a) give a msgbox saying: "In " &labelcell
&":" & cell _
&"exceeded " & othercell &" at "& format(now(),hh:mm:ss), and b) log
this info to a text file. (I might not have goeen all the quotes and &
in the right place....)

thanks again,
Ross


"Tom Ogilvy" wrote in message
...
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