I am using Microsoft Excell 2003 SP1.

I have a UDF in a normal Module that reads all the worksheet names in
the active workbook and puts them into an array. A Summary worksheet
contains a formula copied to several cells in a column that uses this
UDF to list the sheet names. The formulas end in (wihout the quotes)
"&T(RAND())" to make it volatile so that it updates whenever worksheet
names are changed.

I also have a Workbook_SheetChange macro that writes data into an
adjacent cell when a target cell value is changed. I use
Application.EnableEvents=False at the beginning and
Application.EnableEvents = True at the end of the macro. The target
cell has a validation drop down list.

When the line of the macro is reached where it is supposed to write to
the adjacent cell, before the data is actually written to the cell, the
UDF is called and executes.

This is not a problem if valid data is entered into the target cell via
keyboard entry. The UDF executes and execution is returned to the
macro which then finishes normally. But if the data is selected from
the validation dropdown list, execution never returns from the UDF back
to the macro. The data never gets written to the adjacent cell and the
Application.EnableEvents remains False.

Is there anything I can do to keep the UDF from taking over the
execution of the code? The Application.EnableEvents = False does not do
it. Any other VBA statements that would do the job?

Thanks,

Larry

Design the UDF not to cause an error

Public function ABC()
On error Resume Next



End Function




and

Private Sub Workbook_SheetChange( . . .

On Error goto ErrHandler
Application.EnableEvents = False



' don't write anything to jump over the
' error handler.
ErrHandler:
Application.EnableEvents = True
End Sub


wrote in message
ps.com...
I am using Microsoft Excell 2003 SP1.

I have a UDF in a normal Module that reads all the worksheet names in
the active workbook and puts them into an array. A Summary worksheet
contains a formula copied to several cells in a column that uses this
UDF to list the sheet names. The formulas end in (wihout the quotes)
"&T(RAND())" to make it volatile so that it updates whenever worksheet
names are changed.

I also have a Workbook_SheetChange macro that writes data into an
adjacent cell when a target cell value is changed. I use
Application.EnableEvents=False at the beginning and
Application.EnableEvents = True at the end of the macro. The target
cell has a validation drop down list.

When the line of the macro is reached where it is supposed to write to
the adjacent cell, before the data is actually written to the cell, the
UDF is called and executes.

This is not a problem if valid data is entered into the target cell via
keyboard entry. The UDF executes and execution is returned to the
macro which then finishes normally. But if the data is selected from
the validation dropdown list, execution never returns from the UDF back
to the macro. The data never gets written to the adjacent cell and the
Application.EnableEvents remains False.

Is there anything I can do to keep the UDF from taking over the
execution of the code? The Application.EnableEvents = False does not do
it. Any other VBA statements that would do the job?

Thanks,

Larry