vba 使用唯一值填充组合框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12452026/
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
Populating Combobox with Unique Values
提问by lukeweatherstone
I'm looking to populate a combobox with only unique text values from a column. If a value in the column is empty (i.e. "") then it takes the value from the adjacent column to the left (still making sure it's not a duplicate).
我希望用列中的唯一文本值填充组合框。如果列中的值是空的（即“”），那么它会从左边的相邻列中取值（仍然确保它不是重复的）。
I've embedded a Public Sub within the Userform module to add the items without duplicates:
我在 Userform 模块中嵌入了一个 Public Sub 来添加没有重复的项目：
Public Sub addIfUnique(CB As ComboBox, value As String)
If CB.ListCount = 0 Then GoTo doAdd
Dim i As Integer
For i = 0 To CB.ListCount - 1
If CB.List(i) = value Then Exit Sub
Next
doAdd:
CB.AddItem value
End Sub
However when I try to call the sub, it tells me an object is required. What I've got so far is as follows:
但是，当我尝试调用 sub 时，它告诉我需要一个对象。到目前为止，我所得到的如下：
Worksheets("Scrapers").Activate
Range("M9").Activate
Dim intX As Integer
Dim value As String
push_lt_cbo.Clear
Do Until ActiveCell.Offset(0, -1).value = 0
If ActiveCell.value = "" Then
value = ActiveCell.Offset(0, -1).Text
Call addIfUnique((push_lt_cbo), (value))
Else
value = ActiveCell.Text
Call addIfUnique((CB), (value))
End If
Loop
Any help would be much appreciated!
任何帮助将非常感激！
LW
长尾
采纳答案by ray
You're close:
你很接近：
Option Explicit 'Add this if you don't already have it
Private Sub UserForm_Initialize()
Worksheets("Scrapers").Activate
Range("M9").Activate
Dim intX As Integer
Dim value As String
push_lt_cbo.Clear
'Your loop will never end like this:
'Do Until ActiveCell.Offset(0, -1).value = 0
'Instead use a variable:
Dim rowOffset As Integer
rowOffset = 0
Do Until ActiveCell.Offset(rowOffset, -1).value = 0
'There was a lot of extra stuff here. Simplifying:
value = ActiveCell.Offset(rowOffset, -1).value
'Remove optional CALL keyword.
'Also remove paranthesis; they caused the error:
addIfUnique push_lt_cbo, value
'increment offset:
rowOffset = rowOffset + 1
Loop
End Sub
'Use 'msforms.ComboBox' to clarify.
Public Sub addIfUnique(CB As msforms.ComboBox, value As String)
If CB.ListCount = 0 Then GoTo doAdd
Dim i As Integer
For i = 0 To CB.ListCount - 1
If CB.List(i) = value Then Exit Sub
Next
doAdd:
CB.AddItem value
End Sub
