Select item in dropdown using VBA

[Boss]

Hi,

I have data validation done in a cell. There are ten items in the dropdown
list.

I wish to write a code which selects the item from dropdown as put it in the
cell.

Please help me with a macro which selects fourth itme in the dropdown and
puts it in the cell.

Thanks!
Boss

 

[Bernie Deitrick]

Boss,

For XL2000 onward (split wasn't available in XL97)

Written for the activecell:

ActiveCell.Value = Split(ActiveCell.Validation.Formula1, ",")(3)

Written for a specific cell:

Range("A10").Value = Split(Range("A10").Validation.Formula1, ",")(3)

HTH,
Bernie
MS Excel MVP

 

[Rick Rothstein]

You are using a Validation List (items displayed in list are from a defined
range such as $Z$1:$Z$10), right? If so, for the active cell...

ActiveCell.Value = Range(ActiveCell.Validation.Formula1)(3).Value

or for a specified cell (say A3)...

Range("A3").Value = Range(Range("A3").Validation.Formula1)(3).Value

 

[Boss]

Thankyou bith for teh reply...
You guys made my day.. !!

Thanks!
Boss