UDF is cancelling (preventing) SheetChange Event
 

I could really use some help on this one. I'm stumped!

I just created a UDF to do a lookup from a table on a hidden sheet. It's
defined as .volatile to force updating on all sheets where it exists.

Here's the problem. When doing a copy/paste, my Workbook_SheetChange Events
are no longer running!! They still fire when I simply modify a cell's
contents, but not from a copy/paste.

FYI - Within my SheetChange, if a copy/paste occurred, I repair the
formats by copying formats and validations from an identical hidden sheet.
Also, I have alot of INDIRECTS in my code, and I read another thread that
mentioned this as significant when using UDF's

Here's my UDF that's caused the problems.

<code
Function PRCTEXT(Key As String, Optional KeyCol As Single) As Variant
Application.Volatile 'forces recalc of UDF as a volatile function

On Error Resume Next
lookupcol = Application.Match([Entity].Value, [Text_Entities], 0) _
+ IIf(KeyCol = 0, 1, KeyCol)
If Err.Number < 0 Then GoTo EndofFunction

xval = Application.VLookup(Key, [Text_Table], lookupcol, False)
If Err.Number < 0 Then GoTo EndofFunction

EndofFunction:
PRCTEXT = IIf(Len(xval) = 0, "", xval)
If trap_errors Then: On Error GoTo Err_Handler: Else: On Error GoTo 0
Exit Function

Err_Handler:
Call Error_Handler
End Function
</code

Thanks in advance for any help!

--
Message posted via http://www.officekb.com