How to add validation by VBA

[michelle_ho]

I want to set validation of a cell from a list of value in another exce
file. I know that I can do from Data Menu -> Validation. However,
want to do it by writing VBA code.

Example:
In A.xls, cell A1 = Apple
cell A2 = Orange
cell A3 = Pear
cell A4 = Banana

Then in B.xls, I want to set cell D3 can have the value only from th
above list,

How can I do so?

Thx a lot

 

[Don Guillett]

You cannot do this directly since datavalidation does not accept it.
HOWEVER, you can name the range in the active workbook that refers to the
range in the source workbook.
insert>name>define>name it mylist>in the refers to box type in
'=[20010112.xls]Data!$A$14:$A$19
now in the data validation cell use LIST and type in =mylist
voila!