Read Dropdown list from Excel column

[ldiowa]

Hi, Am Wed, 14 Aug 2013 09:20:32 -0700 (PDT) schrieb (e-mail address removed): > 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. try: myStr = Sheets("Sheet1").Range("A1").Validation.Formula1 myArr = Split(myStr, ";") Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office2003 SP2 /2007 Ultimate SP2

Yes, oSheet was defined as
Excel.WorkSheet oSheet = EXL.Sheet["Shhet1"];
and still received the error.

Thanks again for your help.

 

[Claus Busch]

Hi Garry,

Am Wed, 14 Aug 2013 12:41:25 -0400 schrieb GS:

Does Formula1 contain a
delimited string or a range ref? If former, what delimiter is used? If
latter, is the range ref a defined name or an address? If former then
its content must be evaluated OR the range must be enumerated for its
items. Adding to complexity is whether the range ref includes its
sheetname, and what its scope is.

I tried it with both - values in validation and range ref. Both gives
back the values with semicolon as separator.
One time in validation I entered:
Test1;Test2;Test3
and another time I used
=myList
Semicolon is in german system the row separator. myList is vertical.


Regards
Claus B.

 

[Claus Busch]

Hi Garry,

Am Wed, 14 Aug 2013 18:46:14 +0200 schrieb Claus Busch:

Test1;Test2;Test3
and another time I used
=myList

my bad. I tested the hard coded validation two times
With a named range (=myList) it works:
Sub Test3()
Dim myArr As Variant
Dim myStr As String
Dim rngC As Range
Dim i As Integer

myStr = Replace(Range("A1").Validation.Formula1, "=", "")
ReDim myArr(Range(myStr).Cells.Count - 1)
For Each rngC In Range(myStr)
myArr(i) = rngC
i = i + 1
Next
End Sub


Regards
Claus B.

 

[GS]

Hi, Am Wed, 14 Aug 2013 09:20:32 -0700 (PDT) schrieb (e-mail address removed): >
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.
try: myStr = Sheets("Sheet1").Range("A1").Validation.Formula1 myArr =
Split(myStr, ";") Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Yes, oSheet was defined as
Excel.WorkSheet oSheet = EXL.Sheet["Shhet1"];
and still received the error.

Thanks again for your help.

Sheet is a workbook object. Workbook is an Excel object. So...

Dim wksDVList, wksTarget, wkbSource, rngDVList, vDVList
Set wkbSource = appXL.Workbooks("MyWkb")
Set wksDVList = wkbSource.Sheets("DVList")
Set wksTarget = wkbSource.Sheets("Target")
Set rngDVList = wksTarget.Cells(1, 1)
vDVList = rngDVList.Validation.Formula1

...will give you the contents of the dropdown list. This will be either
a hard-entered delimited string which you'll have to determine what
delimiter is used, OR a ref to a range where the list is stored. (See
my other replies for more about the complexities involved in evaluating
the range ref)


--
Garry

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

 

[ldiowa]

Hi Claus,

Appreciate your help very much. However, your code does not work. The errormessage is the same as what I mentioned earlier. I understood workBook and WorkSheet. Yes, dropdown list is on workSheet (your wksTarget here) and the data to make the lidt is on another Sheet (your wksDVList here). I am not sure what I miss. I can email you my excel file for you to take a look ifyou like.
Thanks again.

 

[Claus Busch]

Hi,

Am Wed, 14 Aug 2013 09:43:09 -0700 (PDT) schrieb (e-mail address removed):

Yes, oSheet was defined as
Excel.WorkSheet oSheet = EXL.Sheet["Shhet1"];
and still received the error.

perhaps you see it better in a workbook.
Look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "Validation" and rightclick and download it


Regards
Claus B.

 

[ldiowa]

Hi, Am Wed, 14 Aug 2013 09:43:09 -0700 (PDT) schrieb (e-mail address removed): > Yes, oSheet was defined as > Excel.WorkSheet oSheet = EXL.Sheet["Shhet1"]; > and still received the error. perhaps you see it better in a workbook. Look here: https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326 for the workbook "Validation" and rightclick and download it Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2

Yes, I did downloaded your Excel file of "Validation". Yours lists are made up from the range on the same sheet. The list on my excel file is made up from another sheet. I still don't get why the code does not work with myfile. As I said.

oSheet.Range("A1").Validation.Formula1 generated the error. I tried Formula2 and received the same error.

 

[Claus Busch]

Hi,

Am Wed, 14 Aug 2013 12:44:57 -0700 (PDT) schrieb (e-mail address removed):

Yes, I did downloaded your Excel file of "Validation". Yours lists are made up from the range on the same sheet. The list on my excel file is made up from another sheet. I still don't get why the code does not work with my file. As I said.

oSheet.Range("A1").Validation.Formula1 generated the error. I tried Formula2 and received the same error.

I changed it and now is the list for the validation in another sheet.
But for the validation the name for the list must be defined global. And
so you don't need a reference to a sheet.


Regards
Claus B.

 

[GS]

Hi,

Am Wed, 14 Aug 2013 09:43:09 -0700 (PDT) schrieb (e-mail address removed):

Yes, oSheet was defined as
Excel.WorkSheet oSheet = EXL.Sheet["Shhet1"];
and still received the error.

perhaps you see it better in a workbook.
Look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "Validation" and rightclick and download it


Regards
Claus B.

What I get from all this is an automation error is being raised because
of unqualified refs. Hard to say without knowing how oSheet is ref'd
down through.

I get no errors querying the Formula1 property whether hard-entered
delimited string OR named range ref, OR just range ref if on same
sheet. The only way I know to use a list stored on a different sheet is
to assign a global scope name to it. So...

In the Immediate Window:
?Sheets("Sheet2").Cells(1,1).Validation.Formula1
=List1

...where List1 refs A1:A5 on Sheet1. (a b c d e)

When hard-entered a delimited string on Sheet2 DV (a,b,c,d,e) it
returns the delimited string.

So if the property returns a range ref then...

Dim sDVList, vDVList, n&
sDVList = Sheets("Sheet2").Cells(1,1).Validation.Formula1 'wksTarget
vDVList = wkbSource.Range(Mid$(sDVList, 2) '2D array of the DV list
For n = LBound(vDVList) To UBound(vDVList)
MsgBox vDVList(n, 1)
Next

...which gives me these 5 MsgBox prompts...

a
b
c
d
e

When I use a local range ref (ie: =A1:A5) I get that back...
=A1:A5

If the local ref is a local scope defined name then I get that back...
=DVList

...so I guess the easiest way to test if the list is a delimited string
is to check it for common delimiters and exit the loop when found.

Likewise the named range. Check the sheet 1st for a local scope name.
If found the the parent is the sheet containing the DV cell. Otherwise
the parent is the workbook.

Use the Like function to test a local range ref...

If sDVList Like "=A1:A2" Then _
vDVList = wksTarget.Range(Mid$(sDVList, 2))

HTH

--
Garry

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

 

[Claus Busch]

Hi Garry,

Am Wed, 14 Aug 2013 16:34:46 -0400 schrieb GS:

If the local ref is a local scope defined name then I get that back...
=DVList

in version xl2007 or earlier list on other sheets has to be named with a
GLOBAL name.
xl2010 or later you can refer to a range on another sheet, like
Sheet2!A1:A10
Therefore it is important to know what xl version is used and how is
referred (with name or with a range)


Regards
Claus B.

 

[Claus Busch]

Hi Garry,

Am Wed, 14 Aug 2013 22:43:30 +0200 schrieb Claus Busch:

Therefore it is important to know what xl version is used and how is
referred (with name or with a range)

and I don't know if validation can be opened in another language system
without problems because of the separators.


Regards
Claus B.

 

[GS]

Hi Garry,

Am Wed, 14 Aug 2013 16:34:46 -0400 schrieb GS:


in version xl2007 or earlier list on other sheets has to be named with a
GLOBAL name.
xl2010 or later you can refer to a range on another sheet, like
Sheet2!A1:A10
Therefore it is important to know what xl version is used and how is
referred (with name or with a range)


Regards
Claus B.

Thanks, Claus! I did not know this limitation was removed in v2010. In
this case the sheetname will need to be parsed as I noted in an earlier
reply!

--
Garry

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

 

[GS]

Hi Garry,

Am Wed, 14 Aug 2013 22:43:30 +0200 schrieb Claus Busch:


and I don't know if validation can be opened in another language system
without problems because of the separators.


Regards
Claus B.

Validation.Formula1 is a read only property and so can be accessed via
automation in a normal fashion. What's important is to get the
qualified refs correct! IMO, this is the #1 problem with COM
automation; people don't get the object refs properly initialized and
so nothing works. (Same happens using VB6 or C++)

--
Garry

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

 

[isabelle]

hi Hugh,

i made a model using a name for the validation

http://cjoint.com/?CHow1NBP9ko

isabelle

Le 2013-08-14 15:44, (e-mail address removed) a écrit :

 

[GS]

Hi Garry,

Am Wed, 14 Aug 2013 12:41:25 -0400 schrieb GS:



I tried it with both - values in validation and range ref. Both gives
back the values with semicolon as separator.
One time in validation I entered:
Test1;Test2;Test3
and another time I used
=myList
Semicolon is in german system the row separator. myList is vertical.


Regards
Claus B.

Interesting! I get back exactly what's in the DV refedit box. Using
your example for the named list I get back...

=myList

--
Garry

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

 

[isabelle]

if the source range was named and that name was used to create the
validation
this code:

Range("A1").Validation.Formula1

returns the name of the source
then you can use it like this:

Range(Range("A1").Validation.Formula1).Copy Range("A1").Offset(0, 3)

isabelle

Le 2013-08-14 16:57, isabelle a écrit :

 

[Claus Busch]

Hi Garry,

Am Wed, 14 Aug 2013 17:37:03 -0400 schrieb GS:

Interesting! I get back exactly what's in the DV refedit box.

yes, you always get back what is in the refedit box.
Hard coded the items or the range name (=myList) or the range itself
(=$H$1:$H$5)
You can check for the "=" to see if you can work with the items at once
or you have to work with the ranges.

But xl2010 or xl2013 you could get e.g. =Sheet2!myList or
=Sheet3!$A$1:$A$10 and you have to do a little workaround


Regards
Claus B.

 

[ldiowa]

hi Hugh, i made a model using a name for the validation http://cjoint.com/?CHow1NBP9ko isabelle Le 2013-08-14 15:44, (e-mail address removed) a écrit : > > oSheet.Range("A1").Validation.Formula1 generated the error. I tried Formula2 and received the same error. >

Hi Isabelle,

Thanks very much for your rhelp. My apology for almost missing your reply.I am new to forum and Excel programing. Yes, I ran your model. Yes, ValList did appear when I place the cursor over cell "C1". Your Macro worked too. I need to translate your macro into C#. to read the content of the dropdown list. My question is: where can I find my "ValList"? Can you help me to show you can programmatically select a item for cell A1?

Again, thanks so much.

Hugh

 

[GS]

Hi Garry,

Am Wed, 14 Aug 2013 17:37:03 -0400 schrieb GS:


yes, you always get back what is in the refedit box.
Hard coded the items or the range name (=myList) or the range itself
(=$H$1:$H$5)
You can check for the "=" to see if you can work with the items at once
or you have to work with the ranges.

But xl2010 or xl2013 you could get e.g. =Sheet2!myList or
=Sheet3!$A$1:$A$10 and you have to do a little workaround


Regards
Claus B.

Yes, I agree! I'm currently working on a reusable wrapper function that
will process the Formula1 content based on hard-entered list or range
ref. I'll post it for anyone interested to have 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

 

[ldiowa]

if the source range was named and that name was used to create the validation this code: Range("A1").Validation.Formula1 returns the name of the source then you can use it like this: Range(Range("A1").Validation.Formula1).Copy Range("A1").Offset(0, 3) isabelle Le 2013-08-14 16:57, isabelle a écrit : > hi Hugh, > > i made a model using a name for the validation > > http://cjoint.com/?CHow1NBP9ko > > isabelle > > Le 2013-08-14 15:44, (e-mail address removed) a écrit : > >> >> oSheet.Range("A1").Validation.Formula1 generatedthe error. I tried >> Formula2 and received the same error. >>

Hi Isabelle,

It looks like that the dropdown List on my file is not named.
I ran your model and irt worked well. and I did see "ValList". I ran my simple code of

string s = oSheet.Range["A1"].Balidation.Formula1;
agaist your file and received "ValList", and received the error message of Formula1 not found. I know that you already helped me a lot, any more suggestions?

Hugh