Get Text Value of Dropdown

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tara H - 18 Apr 2008 13:27 GMT

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?

Reply to this Message

Dave Peterson - 18 Apr 2008 13:58 GMT

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

> 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.
[quoted text clipped - 19 lines]
> string without having to go back and compare the index to the list originally
> used to populate the dropdown?

Signature

Dave Peterson

Reply to this Message

Tara H - 18 Apr 2008 14:08 GMT

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.
[quoted text clipped - 40 lines]
> > string without having to go back and compare the index to the list originally
> > used to populate the dropdown?

Reply to this Message

dan dungan - 18 Apr 2008 17:17 GMT

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

Reply to this Message