Hi,

How to retrieve all items in a dropdown list in a Excel cell using C#? Furthermore, can the SelectedValue of the dropdown be set using C#? I am using Office 2007.

Thanks very much for your help.

Hugh

Hi,

How to retrieve all items in a dropdown list in a Excel cell using C#?
Furthermore, can the SelectedValue of the dropdown be set using C#? I am
using Office 2007.

Thanks very much for your help.

Hugh

Try doing it manually in Excel while the macro recorder is running.
This will give you the correct refs to use in your code. Just make the
necessary language syntax adjustments and you're done!

--
Garry

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

Hi Garry,

Thanks for your help. I will take a look your link. For the second part, I used
oSheet.Range["A1"].Value = "Value", it does not work. Howevr, the same syntax works for regular cells (cells that don't have a dropdown associated with). Thank again.

Hugh

Hi Garry,

Thanks for your help. I will take a look your link. For the second part, I
used oSheet.Range["A1"].Value = "Value", it does not work. Howevr, the same
syntax works for regular cells (cells that don't have a dropdown associated
with). Thank again.

Hugh

Note that the cell with a DV list (dropdown) can only hold values from
its DV list when being edited. That means if your text "Value" isn't in
the DV list then you can't insert it, and so it appears as though
nothing happened - assuming notification prompts for that cell are
turned off. Otherwise, you'll generate the 'invalid input' prompt.

--
Garry

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

Hi Gary,

I don't follow you. I already have a excel file that has a dropdown list in a cell. I want to retrieve all list items using C# and don't know how to do it. Yes, I can manually click the arrow on the cell and all items in the dropdown will be in display. However, I don't know if there is a "procedure" to get these values. Thanks.

Hugh

hi ,Hugh,

with vba,

x = Range("A1").Validation.Formula1

isabelle

Le 2013-08-13 16:33, a écrit :
Hi Gary,

I don't follow you. I already have a excel file that has a dropdown list in a cell.

I want to retrieve all list items using C# and don't know how to do
it. Yes,

I can manually click the arrow on the cell and all items in the
dropdown will be in display. However,

I don't know if there is a "procedure" to get these values. Thanks.

Hugh

On Tuesday, August 13, 2013 4:21:04 PM UTC-5, isabelle wrote:
hi ,Hugh, with vba, x = Range("A1").Validation.Formula1 isabelle Le 2013-08-13 16:33, a écrit : Hi Gary, I don't follow you. I already have a excel file that has a dropdown list in a cell. I want to retrieve all list items using C# and don't know how to do it. Yes, I can manually click the arrow on the cell and all items in the dropdown will be in display. However, I don't know if there is a "procedure" to get these values. Thanks. Hugh

Hi Isbelle,

Thanks for your reply. I tried the method you suggested prior my post. It return a null.

Hi Gary,

I don't follow you. I already have a excel file that has a dropdown list in
a cell. I want to retrieve all list items using C# and don't know how to do
it. Yes, I can manually click the arrow on the cell and all items in the
dropdown will be in display. However, I don't know if there is a "procedure"
to get these values. Thanks.

Hugh

Ah, I see now what the issue is. Look in the Object Browser under
Range().Validation to see the properties/method available. Press F1 for
further info/examples to see what you need to grab from for your list.
For example, if the list is hard-entered items then...

oSheet.Range("A1").ValidationFormula1

...returns the list as entered. So if the list is '1,2,3,4,5' then it
might be auto-typed as a string "1,2,3,4,5" when put into a variable.

If it holds a ref to a range where the list is stored then it will
return the range address prepended with an equal symbol...

=$F$1:$J$1

...that you can parse however you want to get the list items however you
want.

If it holds a ref to a defined name where the list is stored then it
gets a bit more complex depending on whether the defined name refs a
range or just stores the list. Either must be queried via the
Application.Evaluate method to returns the value in its 'RefersTo'
property. This will be a range ref or the list items and so your code
will need to parse accordingly.

In summary, you need to evaluate whether Formula1 contains a range
address or defined name. If the latter you need to evaluate whether the
defined name is local (sheet level) or global (workbook level) in
scope. (God forbid it's defined both ways, but if so I believe the
local scope name has precedence over global!!) Then you need to
determine if the defined name refs a range or holds a delimited string.

Note that there is only 1 method for Range().Validation, which is
'Modify'!

--
Garry

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

Oops.., typo!

Ah, I see now what the issue is. Look in the Object Browser under
Range().Validation to see the properties/method available. Press F1 for
further info/examples to see what you need to grab from for your list. For
example, if the list is hard-entered items then...

oSheet.Range("A1").Validation.Formula1

..returns the list as entered. So if the list is '1,2,3,4,5' then it might be
auto-typed as a string "1,2,3,4,5" when put into a variable.

If it holds a ref to a range where the list is stored then it will return the
range address prepended with an equal symbol...

=$F$1:$J$1

..that you can parse however you want to get the list items however you want.

If it holds a ref to a defined name where the list is stored then it gets a
bit more complex depending on whether the defined name refs a range or just
stores the list. Either must be queried via the Application.Evaluate method
to returns the value in its 'RefersTo' property. This will be a range ref or
the list items and so your code will need to parse accordingly.

In summary, you need to evaluate whether Formula1 contains a range address or
defined name. If the latter you need to evaluate whether the defined name is
local (sheet level) or global (workbook level) in scope. (God forbid it's
defined both ways, but if so I believe the local scope name has precedence
over global!!) Then you need to determine if the defined name refs a range or
holds a delimited string.

Note that there is only 1 method for Range().Validation, which is 'Modify'!

--
Garry

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

On Tuesday, August 13, 2013 4:29:41 PM UTC-5, GS wrote:
Hi Gary, I don't follow you. I already have a excel file that has a dropdown list in a cell. I want to retrieve all list items using C# and don't know how to do it. Yes, I can manually click the arrow on the cell and all items in the dropdown will be in display. However, I don't know if there is a "procedure" to get these values. Thanks. Hugh Ah, I see now what the issue is. Look in the Object Browser under Range().Validation to see the properties/method available. Press F1 for further info/examples to see what you need to grab from for your list. For example, if the list is hard-entered items then... oSheet.Range("A1").ValidationFormula1 ..returns the list as entered. So if the list is '1,2,3,4,5' then it might be auto-typed as a string "1,2,3,4,5" when put into a variable. If it holds a ref to a range where the list is stored then it will return the range address prepended with an equal symbol... =$F$1:$J$1 ..that you can parse however you want to get the list items however you want. If it holds a ref to a defined name where the list is stored then it gets a bit more complex depending on whether the defined name refs a range or just stores the list. Either must be queried via the Application.Evaluate method to returns the value in its 'RefersTo' property. This will be a range ref or the list items and so your code will need to parse accordingly. In summary, you need to evaluate whether Formula1 contains a range address or defined name. If the latter you need to evaluate whether the defined name is local (sheet level) or global (workbook level) in scope. (God forbid it's defined both ways, but if so I believe the local scope name has precedence over global!!) Then you need to determine if the defined name refs a range or holds a delimited string. Note that there is only 1 method for Range().Validation, which is 'Modify'! -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion

Hi Gary,

Thanks very much for your help. I need to give it more time to digest. As a quick note, I tried the method of "Formula1" prior my post. It returned null. The method was suggested by Isbelle, aanother helper here. I found that the dropdown was referenced from another sheet in the same excel file. I can easily retrieve the values from there. Retrieving dropdown list is only technical chanllenge for me now. However, I still don't know how to programmatically set a value for the dropdown. I tried:
oSheet.Range["A1"].Value = "A1". it did not work and no error message. Thanks ver much for your help again.

Hugh

On Tuesday, August 13, 2013 4:29:41 PM UTC-5, GS wrote:
Hi Gary, I don't follow you. I already have a excel file that has a dropdown list in a cell. I want to retrieve all list items using C# and don't know how to do it. Yes, I can manually click the arrow on the cell and all items in the dropdown will be in display. However, I don't know if there is a "procedure" to get these values. Thanks. Hugh Ah, I see now what the issue is. Look in the Object Browser under Range().Validation to see the properties/method available. Press F1 for further info/examples to see what you need to grab from for your list. For example, if the list is hard-entered items then... oSheet.Range("A1").ValidationFormula1 ..returns the list as entered. So if the list is '1,2,3,4,5' then it might be auto-typed as a string "1,2,3,4,5" when put into a variable. If it holds a ref to a range where the list is stored then it will return the range address prepended with an equal symbol... =$F$1:$J$1 ..that you can parse however you want to get the list items however you want. If it holds a ref to a defined name where the list is stored then it gets a bit more complex depending on whether the defined name refs a range or just stores the list. Either must be queried via the Application.Evaluate method to returns the value in its 'RefersTo' property. This will be a range ref or the list items and so your code will need to parse accordingly. In summary, you need to evaluate whether Formula1 contains a range address or defined name. If the latter you need to evaluate whether the defined name is local (sheet level) or global (workbook level) in scope. (God forbid it's defined both ways, but if so I believe the local scope name has precedence over global!!) Then you need to determine if the defined name refs a range or holds a delimited string. Note that there is only 1 method for Range().Validation, which is 'Modify'! -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion

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.

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