Oops.., typo!

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").Validation.Formula1

..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