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

问题描述

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

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.

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

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

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 1 or 2 (the index number). I would like it to show either msg1 or msg2.

这可能吗?

推荐答案

一些选项.

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

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

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

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

Another option is not to use the LinkedCell property and use a macro to write the value. Assign this macro the Dropdown

Sub ShowDDResult()

    Dim dd As DropDown

    Set dd = ActiveSheet.DropDowns(Application.Caller)

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

End Sub

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

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

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

这篇关于从下拉框中返回文本而不是索引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！