On Wednesday, August 14, 2013 10:02:23 AM UTC-5, GS wrote:
Thanks all, especially, Gary for your help. I tried all tips and did not
work. However, your help did broaden my knownledge about Excel programing.
As I said, Practically, I can retrieve info from the sheet where the
dropdown listitems from. I need to know how to set selecteItem in
dropdown and will post a new topic. Thanks again for your help. You're
welcome. Glad to be whatever help I can! You can get the range ref from...
Dim sRngRef$ sRngRef = oSheet.Cells(1, 1).Validation.Formula1 ..as both I
and Isabelle explained. Once you have the range ref you can get the list
items from... Dim iPos%, sWksName$, sRngAddr$, vDVList, n& iPos =
InStr(sRngRef, "!") sWksName = Replace(Left$(sRngRef, iPos - 1), "'", "")
sRngAddr = Mid$(sRngRef, iPos + 1) vDVList =
wkbSource.Sheets(sWksName).Range(sRngAddr) ..which loads the list into a
1-based 2D array. You can access the items in the normal way... Dim
sListItem$ For n = LBound(vDVList) To UBound(vDVList) sListItem =
vDVList(n, 1) Next Note that vDVList is data type 'Variant' so the size of
the array is automagic based on the size of the range being assigned to it.
-- Garry Free uenet access at http://www.eternal-september.org Classic VB
Users Regroup comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I need to take sometime to learn your code. Are these "$", "%" and "&"
legitment VB syntax? or, they were system added junks?

Sorry for the brevity.., it's a habit I've become accustomed to! Yes,
those symbols are valid VB[A] syntax for data types...

String $
Integer %
Long &

Also, I forgot to declare the object ref to the workbook...

Dim wkbSource As appXL.Workbook

...where appXL is the usual var name I use in VB6 automation. In your
case you may want to declare 'As Object' or 'As Variant' if using late
binding (ie: CreateObject)!

--
Garry

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