Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting Rows during a SheetChange event | Excel Programming | |||
how to handle 'sheetchange' event on 'add-in' | Excel Programming | |||
SheetChange Event | Excel Programming | |||
SheetChange Event | Excel Programming | |||
sheetChange event and list validation | Excel Programming |