If you used a linked cell (on the same sheet or a different sheet), you could
have looked there.

Or...

Option Explicit
Sub show_region()

Dim myDrop As DropDown
Set myDrop = Sheets("report").DropDowns("select_drop")

With myDrop
If .ListIndex = 0 Then
MsgBox "Nothing selected"
Else
MsgBox .List(.ListIndex)
End If
End With

End Sub



Tara H wrote:

I have a dropdown on a worksheet which is populated programatically - I can
put the options into it fine, but I can't get them back out again.
I can get the .value or .listindex without a problem, but I'd rather be
working with the string.

I have been testing using the following:

Sub show_region()

Dim myDrop As Excel.DropDown
Set myDrop = Sheets("report").DropDowns("select_drop")
MsgBox myDrop.Text

End Sub

.Text comes up as a possible completion, but when I try to use it I get
"Unable to get the Text property of the DropDown class". Putting a watch on
myDrop while this is running indicates that the same is true for Caption,
which seemed the only other likely choice.

Can anyone tell me what I'm doing wrong here? Is it even possible to get a
string without having to go back and compare the index to the list originally
used to populate the dropdown?

--

Dave Peterson