vba 从下拉框中返回文本而不是索引号

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

提问by Rippo

I have the following VBA code (from MS Access 2007). The code creates a new workbook and adds a dropdown to a cell. This small snippet adds a drop down to a particular cell and the adds some items to it.

我有以下 VBA 代码（来自 MS Access 2007）。该代码创建一个新工作簿并向单元格添加下拉列表。这个小片段向特定单元格添加了一个下拉列表，并向其中添加了一些项目。

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

This all works great and when I open the spreadsheet I get a combo box where I want and the items are displayed. However when I select an item the from the drop down in Excel the linked cell shows 1or 2(the index number). I would like it to show either msg1or msg2.

这一切都很好，当我打开电子表格时，我会在我想要的地方得到一个组合框，并显示项目。但是，当我从 Excel 的下拉列表中选择一个项目时，链接的单元格会显示1或2（索引号）。我希望它显示msg1或msg2。

Is this possible?

这可能吗？

回答by Dick Kusleika

A few options.

几个选项。

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

您可以在单元格中放置数据验证下拉列表而不是 Dropdown 对象。这将返回实际结果而不是索引。如果你仍然需要一个单独的链接单元格，你可以放一个简单地复制 dv 单元格的公式

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 not to use the LinkedCell property and use a macro to write the value. Assign this macro the Dropdown

另一种选择是不使用 LinkedCell 属性并使用宏来写入值。将此宏分配给下拉菜单

Sub ShowDDResult()

    Dim dd As DropDown

    Set dd = ActiveSheet.DropDowns(Application.Caller)

    ActiveSheet.Cells(row, col + 2).Value = dd.List(dd.Value)

End Sub

That may not be so easy if you're creating the worksheet from scratch from Access because you'd have to add the macro. The final option is to use the ListFillRange property to fill the Dropdown. Put the list in a range and use a formula off of the LinkedCell to pull the date out of the list

如果您从 Access 从头开始​​创建工作表，这可能并不容易，因为您必须添加宏。最后一个选项是使用 ListFillRange 属性来填充下拉列表。将列表放在一个范围内并使用 LinkedCell 的公式将日期从列表中拉出

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

回答by DrBuck

I was trying to find a neater way of doing this so might as well ressurect this question :) Here's how I have been solving this problem. I create an array of the items I want to fill the drop down with. Then using this array you can return the string associated with the index you get from the drop down.

我试图找到一种更简洁的方法来做到这一点，所以不妨重新回答这个问题:) 以下是我解决这个问题的方法。我创建了一个我想要填充下拉列表的项目数组。然后使用此数组，您可以返回与从下拉列表中获得的索引关联的字符串。

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 your drop down:

然后使用这个数组来填充你的下拉列表：

' 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 by using this array again you can use the following code to get the string associate with the drop down index selected:

然后再次使用此数组，您可以使用以下代码获取与所选下拉索引关联的字符串：

' 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