Re: worksheet_change vs. calculate, and worksheet_change not running

[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

 

[Tom Ogilvy]

=if(and(A1>B1,C1>D1),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/excel/content/fileio/fileio.asp
File Access with Visual Basic® for Applications


Look at the help on SetLinkOnData

It says more than I could say.