Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read Dropdown list from Excel column
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to read the contents of an OLEObject (dropdown list) in excel | Excel Programming | |||
Creating a dropdown list within a column | Excel Discussion (Misc queries) | |||
Dropdown List that populates another column | Excel Worksheet Functions | |||
Dropdown List - list item endings not visible if column too narrow | Excel Discussion (Misc queries) | |||
Procedure to make a dropdown list larger so it's easier to read | Excel Discussion (Misc queries) |