Drop down in data validation

Hi,

I have a fairly complex application where I am trying to use data validation for one cell through the use of VBA. It seems to work fine when the application is started by opening the workbook for the first time. But on subsequent iterations, it looks as if data validation is effective, but the drop down button is missing and the list does not show. If I manually enter the correct value, it accepts and an incorrect value causes an error message. I have spent nearly 2 months in various approaches, but in vain.

Here are a few more details about my application:

It is a simulation application wherein at he beginning of the application the user chooses between an internal data set (approx 270 records) or an external data set ( can be from 10 to 200 records). Once that selection is done, another sheet opens where the user can input values for various variables. But one variable has to be assigned a value from the internal/external data sets. Depending on which data set was selected, I would like to provide the list of available values in a dropdown list. After selecting the value, the user then initiates the simulation by clicking on a link. At the end of the simulation, the user has the option to close the workbook or restart the app for a new round of simulation. (This is where the drop down list fails)

Other relevant details are:

The internal data set is a separate worksheet in the workbook. The external data set is a separate workbook which will be opened and data retrieved if and only if the user selected that as an option. The structure of the data table is exactly the same in both sets. As a result, same variables are used in the application regardless of the source of data.

Secondly, these data sets are retrieved into arrays in the application and the values for the drop down list are extracted from the array into a string separated by comma as a separator and provided as the validation list string for data validation. This data string is more than 255 characters long. (The reason I mention this is because I am not sure if this is the issue though I doubt it because the first iteration seems to work regardless of string length.)

Hopefully my need and my difficulty are clear from this description. Because the application is a long application, it will not be easy to provide the code.

Based on this description, I am hoping members may be able to provide some suggestions as to what the cause could be.

The things I have tried are: Using worksheet_change and worksheet_selectionchange, enabling and/or disabling worksheet protection, etc.

Any help is heartily appreciated.

Best Regards,

RaliR

PS: By the way, I generally use Excel 2007, but this is true for Excel 2007 and 2010.