Here's a reusable function that demos how to process
Validation.Formula1 results for the different scenarios. Note that it
can be run from a worksheet cell or called from a sub...

Function GetDV_ListItems(CellRef As Range) As String
Dim sDVFormula1$, vDVList, vTmp, n&
Application.Volatile

sDVFormula1 = CellRef.Validation.Formula1
If sDVFormula1 = "" Then GoTo noDV

If Left(sDVFormula1, 1) = "=" Then '//it's a range ref
sDVFormula1 = Mid(sDVFormula1, 2)
n = InStr(1, sDVFormula1, "!")
If n 0 Then 'it refs another sheet
vTmp = Split(sDVFormula1, "!")
vDVList = Sheets(Replace(vTmp(0), "'", "")).Range(vTmp(1))
Else '//it's a local ref
vDVList = Range(sDVFormula1)
End If 'iPos 0

If UBound(vDVList) = LBound(vDVList) Then 'it's a row list
For n = LBound(vDVList) To UBound(vDVList, 2)
vTmp = vTmp & "," & vDVList(1, n)
Next 'n
Else 'it's a col list
For n = LBound(vDVList) To UBound(vDVList)
vTmp = vTmp & "," & vDVList(n, 1)
Next 'n
End If
sDVFormula1 = Mid(vTmp, 2)
End If

NormalExit:
GetDV_ListItems = sDVFormula1
Exit Function

noDV:
MsgBox "The cell reference you entered has no Data Validation",
vbExclamation
End Function

Note that it uses Application.Volatile so it auto updates to reflect
changes in the DV list's source range. Also, it strips the leading '='
operator from range refs even though this is not necessary in Excel
with VBA. I stripped it here merely to show how other apps using
automation can get a 'clean' range name. In the case where Formula1
includes another sheetname it prepends a fully qualified ref to its
Range object. You will have to do same for all cases using automation.
This means your 'oSheet' variable must ref
'appXL.Workbooks("SoAndSo.xls").Sheets("SuchAndSuc h").Cells(n,
n).Validation.Formula1 so you have a fully qualified path to the
property value you're after.

I stand corrected in my assertion that code can't put non-DVList values
in a cell with DV! This is not true as I just tested your task and I
was able to enter any value I wanted into the cells with DV. That means
the restriction is only via the UI.

I like Isabelle's approach of using the Validation.Formula1(ListIndex)
because it makes searching the list for a specific entry via looping a
very easy implementation if you need to grab a specific value in code
based on user input criteria.

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion