VBA Code to read selection from a Validation List

[Greg J]

Hi,

Excel 2003.

I have a cell on a worksheet that is set up as a dropdown list (Tools >
Validation > List).

The list is in Sheet1 at cell A1 and I named the range MyListSelection
The data range that populates the dropdown list is called MyListRange
(exists on another sheet)

From VBA code I am attempting to read the value selected in the

MyListSelection but always comes up blank although it displays the
selected text from the list.

How can I retrieve the selected text in the MyListSelection range?

Thanks in advance.

 

[Dave Peterson]

Since you're using Data|Validation, the value is right in the cell:

With Worksheets("sheet1")
msgbox .range("a1").value
end with

 

[Greg J]

Hi Dave,

Thanks for the prompt response. Perhaps in future I should check that
my named range is actually pointing to the required cell (duh!). All
works fine now.

I feel like a bit of a ditz!