从下拉框中返回文本，而不是索引号 [英] Return the text from a dropdown box rather than the index number

问题描述

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

  Dim myRng As Range 
 Dim myDD As Dropdown 
设置myRng = wSheet.Cells（row，col）
使用myRng 
设置myDD = .Parent.DropDowns.Add（Left：=。Left，Top：=。顶部，宽度：=。宽度，高度：=。高度）
 myDD.AddItemmsg1
 myDD.AddItemmsg2
 myDD.LinkedCell = .Parent.Cells（row，col + 2）.Address（external：= True）
结束
  

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

这是可能吗？

解决方案

几个选项。 p>

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

  Sub MakeDv（）
 
 Dim wSheet As Worksheet 
 Dim myRng As Range 
 
设置wSheet = ActiveSheet 
 
设置myRng = wSheet.Cells（row，col）
 myRng.Validation.Add xlValidateList，，，msg1，msg2
 wSheet.Cells（row，col + 2）.Formula ==& myRng.Address 
 
 End Sub 
  

另一个选项是不使用LinkedCell属性并使用宏来写入值。分配这个宏下拉菜单

  Sub ShowDDResult（）
 
 Dim dd As DropDown 
 
设置dd = ActiveSheet.DropDowns（Application.Caller）
 
 ActiveSheet.Cells（row，col + 2）.Value = dd.List（dd.Value）
 
 End Sub 
  

如果您从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 
 
设置wSheet = ActiveSheet 
设置rList = wSheet.Range（D1：D2）
 
设置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 
  

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

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.

Is this possible?

解决方案

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

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

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

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

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