从下拉框中返回文本,而不是索引号 [英] Return the text from a dropdown box rather than the index number
问题描述
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屋!