On Tuesday, August 13, 2013 4:29:41 PM UTC-5, GS wrote:
Hi Gary, I don't follow you. I already have a excel file that has a dropdown list in a cell. I want to retrieve all list items using C# and don't know how to do it. Yes, I can manually click the arrow on the cell and all items in the dropdown will be in display. However, I don't know if there is a "procedure" to get these values. Thanks. Hugh Ah, I see now what the issue is. Look in the Object Browser under Range().Validation to see the properties/method available. Press F1 for further info/examples to see what you need to grab from for your list. For example, if the list is hard-entered items then... oSheet.Range("A1").ValidationFormula1 ..returns the list as entered. So if the list is '1,2,3,4,5' then it might be auto-typed as a string "1,2,3,4,5" when put into a variable. If it holds a ref to a range where the list is stored then it will return the range address prepended with an equal symbol... =$F$1:$J$1 ..that you can parse however you want to get the list items however you want. If it holds a ref to a defined name where the list is stored then it gets a bit more complex depending on whether the defined name refs a range or just stores the list. Either must be queried via the Application.Evaluate method to returns the value in its 'RefersTo' property. This will be a range ref or the list items and so your code will need to parse accordingly. In summary, you need to evaluate whether Formula1 contains a range address or defined name. If the latter you need to evaluate whether the defined name is local (sheet level) or global (workbook level) in scope. (God forbid it's defined both ways, but if so I believe the local scope name has precedence over global!!) Then you need to determine if the defined name refs a range or holds a delimited string. Note that there is only 1 method for Range().Validation, which is 'Modify'! -- 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 Gary,

Thanks very much for your help. I need to give it more time to digest. As a quick note, I tried the method of "Formula1" prior my post. It returned null. The method was suggested by Isbelle, aanother helper here. I found that the dropdown was referenced from another sheet in the same excel file. I can easily retrieve the values from there. Retrieving dropdown list is only technical chanllenge for me now. However, I still don't know how to programmatically set a value for the dropdown. I tried:
oSheet.Range["A1"].Value = "A1". it did not work and no error message. Thanks ver much for your help again.

Hugh