Get Text Value of Dropdown

[Tara H]

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]

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]

Thank you, that was exactly what I needed!

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

 

[dan dungan]

Which version of excel?

I pasted your code in to a sheet module in Excel 2000. Pressing F1
returned the following:

Hidden Objects


Objects that have been hidden in the Microsoft Excel 97 Visual Basic
object model are listed in the following table. These objects are
supported only for backward compatibility; for new code, you should
use the replacement functionality provided in Microsoft Excel 97. To
view hidden objects in the Object Browser, right-click in the Object
Browser window and click Show Hidden Members on the shortcut menu. For
more information about the changes to the Microsoft Excel 97 object
model, see one of the following topics:


Hidden objects

Button, Buttons, CheckBox, CheckBoxes, DialogFrame, DropDown,
DropDowns, EditBox, EditBoxes, GroupBox, GroupBoxes, GroupObject,
GroupObjects, ListBox, ListBoxes, OptionButton, OptionButtons,
ScrollBar, ScrollBars, Spinner, Spinners, TextBox, TextBoxes

Replacement

ActiveX controls