7

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.

Community
  • 1
  • 1
karlstackoverflow
  • 3,298
  • 6
  • 30
  • 41
  • You state _"...dropdown box that is selected on an excel form."_ but the link and sample code refer to shapes on a sheet. Which are you trying to do? Also, which version of Excel are you using? – chris neilsen Mar 06 '12 at 05:25
  • I'm using excel 2010. Hmm well in one part of my code I refer to the dropdown as ActiveSheet.Shapes and that part of code works fine. accessing it through dropdowns also works.. – karlstackoverflow Mar 06 '12 at 05:29
  • I am unsure what you are using: have you added a control to the sheet (eg from the Developer tab, Inset a Form or ActiveX control, and if so which one) or are you adding data validation to a cell or range (eg from Data tab, Data Validation, and if so exactly what is the setup) – chris neilsen Mar 06 '12 at 05:41
  • Yeah I added it through the Form controls under Developer tab. – karlstackoverflow Mar 06 '12 at 05:56

3 Answers3

16

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

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • Wow it worked like a charm. I never would have figured that out, I thought it would have been something simple like .SelectedIndex. Thanks Chris. – karlstackoverflow Mar 06 '12 at 06:06
7

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
cyberponk
  • 1,585
  • 18
  • 19
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
Niederee
  • 4,155
  • 25
  • 38