Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Selecting from a Validation Drop Down Box aborts macro
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 -- Lreeder ------------------------------------------------------------------------ Lreeder's Profile: http://www.excelforum.com/member.php...o&userid=26699 View this thread: http://www.excelforum.com/showthread...hreadid=399672 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
drop downs & data validation | Excel Discussion (Misc queries) | |||
changing value of a cell by selecting an item from a drop down list | Excel Worksheet Functions | |||
Drop Down Boxes for Validation Cells are Too Wide | Excel Discussion (Misc queries) | |||
Forms Toolbar vs. Control Toolbox vs. Data Validation for drop dow | Excel Discussion (Misc queries) | |||
How do I set up a drop down menu for a validation list? | Excel Worksheet Functions |