On Wednesday, August 14, 2013 11:03:56 AM UTC-5, Claus Busch wrote:
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

Hi Claus,

Thanks for your help.

I tried
myString = oSheet.Range("A1").Validation.Formula1 many times and always received the error of HRESULT:0x800A0EC. It is a generic Excel error and means that Formula1 not found. Yes, the lititems are from another sheet.