All geek questions in one place

Return selected text from dropdown list

I am trying to return text from a dropdown list selected in an Excel form. I tried a lot of things and the closest I got is returning the index number. Also looked:

Link: Return text from a drop-down list, not an index number

I did not find a working solution on this page. I tried things like:

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

and etc.

+4
source share
3 answers

This will return the current selection from 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

By the way, assigning a variable declared as Dim dd As DropDown will give you intellisense on dd

+6
source

You can also get the name of the caller if the macro is called by the dropdown window itself. This way you don't have to worry about renaming the dropdown lists :)

 Sub Dropdown_OnSelect() Dim dd As DropDown Set dd = ActiveSheet.Shapes(Application.Caller).OLEFormat.Object MsgBox dd.List(dd.ListIndex) End Sub
+3
source

If you cannot Dim as DropDown , I found that this change 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
+1
source

Source: https://habr.com/ru/post/1399922/

More articles:


All Articles