On Wednesday, August 14, 2013 12:17:38 PM UTC-5, GS wrote:
On Wednesday, August 14, 2013 11:33:26 AM UTC-5, Claus Busch wrote: Hi, Am Wed, 14 Aug 2013 09:20:32 -0700 (PDT) schrieb : myString = oSheet.Range("A1").Validation.Formula1 many times and always received the error of HRESULT:0x800A0EC. It is a generic Excel error and means that Formula1 not found. Yes, the lititems are from another sheet.. try: myStr = Sheets("Sheet1").Range("A1").Validation.Formula1 myArr = Split(myStr, ";") Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 Yes, oSheet was defined as Excel.WorkSheet oSheet = EXL.Sheet["Shhet1"]; and still received the error. Thanks again for your help. Sheet is a workbook object. Workbook is an Excel object. So... Dim wksDVList, wksTarget, wkbSource, rngDVList, vDVList Set wkbSource = appXL.Workbooks("MyWkb") Set wksDVList = wkbSource.Sheets("DVList") Set wksTarget = wkbSource.Sheets("Target") Set rngDVList = wksTarget.Cells(1, 1) vDVList = rngDVList.Validation.Formula1 ...will give you the contents of the dropdown list. This will be either a hard-entered delimited string which you'll have to determine what delimiter is used, OR a ref to a range where the list is stored. (See my other replies for more about the complexities involved in evaluating the range ref) -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion

Hi Claus,

Appreciate your help very much. However, your code does not work. The error message is the same as what I mentioned earlier. I understood workBook and WorkSheet. Yes, dropdown list is on workSheet (your wksTarget here) and the data to make the lidt is on another Sheet (your wksDVList here). I am not sure what I miss. I can email you my excel file for you to take a look if you like.
Thanks again.