vba 获取下拉列表中所选项目的位置（数量）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8458071/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get position (in number) of selected item in dropdown list
提问by jroeleveld
In a dropdown list I have a few items. Can I, when I select an item, get the position of that item in the list as a number?
在下拉列表中，我有几个项目。当我选择一个项目时，我可以以数字的形式获取该项目在列表中的位置吗？
回答by Doug Glancy
If you are looking for the index of a Data Validation list, this is what I'd do:
如果您正在寻找数据验证列表的索引，这就是我要做的：
Put the following code in the ThisWorkbook module:
将以下代码放入 ThisWorkbook 模块：
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim ValidationIndex As Long
Dim rngTest As Excel.Range
'assumes the data validation is in a cell named "rngTest"
On Error Resume Next
Set rngTest = Sh.Range("rngTest")
If rngTest Is Nothing Then
Exit Sub
End If
On Error GoTo 0
If Not Intersect(ActiveCell, Sh.Range("rngTest")) Is Nothing Then
ValidationIndex = GetValidationIndex
MsgBox ValidationIndex
End If
End Sub
Put this function in the ThisWorkbook module also, or else in any regular module:
将此函数也放在 ThisWorkbook 模块中，或者放在任何常规模块中：
Function GetValidationIndex() As Long
'returns a 1-based index
Dim rngTest As Excel.Range
Dim varValidationString As Variant
Dim ErrNumber As Long
Dim i As Long
With ActiveCell.Validation
If .Type = xlValidateList Then '3
On Error Resume Next
Set rngTest = ActiveCell.Parent.Range(.Formula1)
'I do this goofy thing with ErrNumber to keep my indenting and flow pretty
ErrNumber = Err.Number
On Error GoTo 0
'if the Validation is defined as a range
If ErrNumber = 0 Then
GetValidationIndex = Application.WorksheetFunction.Match(ActiveCell.Value2, rngTest, 0)
Exit Function
'if the validation is defined by comma-separated values
Else
varValidationString = Split(.Formula1, ",")
For i = LBound(varValidationString) To UBound(varValidationString)
If varValidationString(i) = ActiveCell.Value2 Then
GetValidationIndex = i + 1
Exit Function
End If
Next i
End If
End If
End With
End Function
回答by Tony Dallimore
If you are using a list or combo box, ListIndexwould seem to be what you are after.
如果您使用的是列表或组合框，ListIndex这似乎就是您所追求的。
VB Help for ListIndexproperty: Returns or sets the index number of the currently selected item in a list box or combo box. Read/write Long. Remarks. You cannot use this property with multiselect list boxes.
VBListIndex属性帮助：返回或设置列表框或组合框中当前选定项的索引号。读/写长。评论。您不能将此属性用于多选列表框。
If nothing is selected, ListIndex's value is -1. If memory serves, it is a zero based index.
如果未选择任何内容，则ListIndex的值为-1。如果没记错的话，它是一个基于零的索引。
ListIndexcannot be set at design time so it is not listed in the properties window.
ListIndex不能在设计时设置，因此它不会列在属性窗口中。
When entering your code, type the list box name then dot and the editor displays all the available properties. Scroll down the list, note any that look interesting, then look them up.
输入代码时，键入列表框名称，然后键入点，编辑器将显示所有可用属性。向下滚动列表，注意任何看起来有趣的内容，然后查找它们。
回答by Sacid Karacuha
I think it is not necessary to use a function. You can get it by using only Match function, like in above Doug's answer.
我认为没有必要使用函数。您可以仅使用 Match 函数来获取它，就像上面 Doug 的回答一样。
Dim GetValidationIndex as Integer
Dim rngTest as Range
' Get the validation list
With ActiveCell.Validation
Set rngTest = ActiveCell.Parent.Range(.Formula1)
end with
GetValidationIndex = Application.WorksheetFunction.Match(ActiveCell.Value2, rngTest, 0)
