Return the text from the dropdown, not the index number

Question

I have the following VBA code (from MS Access 2007). The code creates a new book and adds a drop-down list to the cell. This small snippet adds a dropout to a specific cell and adds some elements to it.

Dim myRng As Range Dim myDD As Dropdown Set myRng = wSheet.Cells(row, col) With myRng Set myDD = .Parent.DropDowns.Add(Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height) myDD.AddItem "msg1" myDD.AddItem "msg2" myDD.LinkedCell = .Parent.Cells(row, col + 2).Address(external:=True) End With 

All this works fine, and when I open the spreadsheet, I get the combo box that I want in, and the elements are displayed. However, when I select an item from the drop-down list in Excel, the linked cell shows 1 or 2 (index number). I would like it to show either msg1 or msg2 .

Is it possible?

Answer 1

Several variants.

You can put data validation data in a cell, rather than a Dropdown object. This returns the actual results, not the index. If you still need a separate linked cell, you can put a formula that just copies the dv cell

 Sub MakeDv() Dim wSheet As Worksheet Dim myRng As Range Set wSheet = ActiveSheet Set myRng = wSheet.Cells(row, col) myRng.Validation.Add xlValidateList, , , "msg1,msg2" wSheet.Cells(row, col + 2).Formula = "=" & myRng.Address End Sub 

Another option is to not use the LinkedCell property and use a macro to write the value. Assign this macro to the drop-down list.

 Sub ShowDDResult() Dim dd As DropDown Set dd = ActiveSheet.DropDowns(Application.Caller) ActiveSheet.Cells(row, col + 2).Value = dd.List(dd.Value) End Sub 

It may not be so simple if you create a worksheet from scratch from Access, because you will need to add a macro. The final option is to use the ListFillRange property to populate the drop-down list. Put the list in the range and use the formula with LinkedCell to deduce the date from the list.

 Sub testdd() Dim wSheet As Worksheet Dim myRng As Range Dim myDD As DropDown Dim rList As Range Dim aList(1 To 2, 1 To 1) As String Set wSheet = ActiveSheet Set rList = wSheet.Range("D1:D2") Set myRng = wSheet.Cells(row, col) aList(1, 1) = "msg1": aList(2, 1) = "msg2" rList.Value = aList With myRng Set myDD = .Parent.DropDowns.Add(Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height) myDD.ListFillRange = rList.Address myDD.LinkedCell = wSheet.Cells(row, col + 2).Address wSheet.Cells(row, col + 3).Formula = "=INDEX(" & rList.Address & "," & myDD.LinkedCell & ",1)" End With End Sub 

Answer 2

I tried to find a more accurate way to do this so that he could look at this issue again :) This is how I solved this problem. I am creating an array of elements that I want to fill. Then, using this array, you can return the row associated with the index that you get from the drop-down list.

First, create a function to return an array of strings:

 ' Returns a string array of drop down items function dditems() as string() Dim array(2) As String array(1) = "cats" array(2) = "dogs" dditems = array end function 

Then use this array to populate the drop-down list:

 ' To populate your drop down sub populatedd() dim dd As DropDown dim i As Integer dim itemsArray() As String ' Create the dd object and item array set dd = Worksheets("Sheet1").DropDowns("Drop Down 1") set itemsArray = dditems() ' Loop through the array to populate the drop down for i = 1 to UBound(itemsArray) dd.AddItem (itemsArray(i)) next i end 

Then, using this array again, you can use the following code to associate the string with a dropdown index:

 ' Get the string associated with the index sub showDDResult() dim dd As DropDown dim itemsArray() As String ' Create the dd object and item array set dd = Worksheets("Sheet1").DropDowns("Drop Down 1") set itemsArray = dditems() ' dd.ListIndex returns index, call to array returns correct string MsgBox("Item selected is " & itemsArray(dd.ListIndex)) end