Read Dropdown list from Excel column
Hi Garry,
Am Wed, 14 Aug 2013 11:02:23 -0400 schrieb GS:
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)
if the list is not in the same sheet you have to name the list. I don't
know what seperator data validation is on your system. In german system
I have a semicolon.
And I put the items in an array with:
myStr = Range("A1").Validation.Formula1
myArr = Split(myStr, ";")
Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
|