Return the selected text from a dropdown box

Question

I am trying to return the text from a dropdown box that is selected on an Excel form. I have tried many things and the closest I have gotten is returning the index number. Also had a look at:

Link: Return the text from a dropdown box rather than the index number

I haven't found a working solution on that page. I have tried things such as:

ActiveSheet.DropDowns("DropDown1").Value
ActiveSheet.DropDowns("DropDown1").Text
ActiveSheet.DropDowns("DropDown1").SelectedValue
ActiveSheet.Shapes("DropDown1").Value

etc.

Answer 1

This will return the current selection from the DropDown

Sub TestDropdown()
    Dim ws As Worksheet
    Dim dd As DropDown

    Set ws = ActiveSheet
    Set dd = ws.Shapes("DropDown1").OLEFormat.Object

    MsgBox dd.List(dd.ListIndex)
End Sub

BTW, assigning to a variable declared as Dim dd As DropDown will give you intellisense on dd

Answer 2

You can also get the caller name, if the macro is called by the dropdown box itself. This way you don´t have to worry about renaming the dropdown boxes :)

Sub Dropdown_OnSelect()
    Dim dd As DropDown

    Set dd = ActiveSheet.Shapes(Application.Caller).OLEFormat.Object

    MsgBox dd.List(dd.ListIndex)
End Sub

Answer 3

If you are unable to Dim as DropDown I found that this alteration will work.

Sub TestDropdown()
    Dim ws As Worksheet
    Dim dd As Object

    Set ws = ActiveSheet
    Set dd = ws.DropDowns("DropDown1")

    MsgBox dd.List(dd.ListIndex)
End Sub