在组合框(VBA)中只有唯一的记录 [英] Only unique records in a Combobox (VBA)
问题描述
为此我使用以下代码:
Private Sub MultiPage1_Change()
Dim Rand As Long
Dim ws As Worksheet
设置ws =工作表(BD_IR)
Dim i As Long
Rand = 3
尽管ws.Cells(Rand,3)。值<> 而兰德65536
如果Me.repereche.ListCount<> 0然后
对于i = 0 To(Me.repereche.ListCount)
如果Me.repereche.List(i,0)< Mid(ws.Cells(Rand,3).Value,4,10)然后
Me.Controls(repereche)。AddItem Mid(ws.Cells(Rand,3).Value,4,10)
End If
Next i
ElseIf Me.repereche.ListCount = 0然后
Me.Controls(repereche)。AddItem Mid(ws.Cells(Rand,3).Value ,4,10)
结束如果
Rand = Rand + 1
循环
这个代码的问题(我不知道问题在哪里?)是每当我更改页面,我回到这个组合框的页面...它增加了更多(不是唯一的)和更多的项目。我在哪里错了?
尝试此代码:
Dim ws As Worksheet
Dim rCell As Range
设置ws =工作表(BD_IR)
'//清除组合框
repereche.Clear
使用CreateObject(Scripting.Dictionary)
对于每个rCell在ws.Range(C3,ws.Cells(Rows.Count,C ).End(xlUp))
如果不是.exists(rCell.Value)然后
.Add rCell.Value,Nothing
End If
下一个rCell
repereche.List = .keys
结束
我喜欢这个集合因为您可以检查字典中是否存在该值,而不是使用错误,并将整个集合一次添加到组合框。
I have a combobox where I add some stuff from an Excel sheet with a bunch of stuff. I want only unique records and I want them to be updated when I switch to this page. For that I used the following code:
Private Sub MultiPage1_Change()
Dim Rand As Long
Dim ws As Worksheet
Set ws = Worksheets("BD_IR")
Dim i As Long
Rand = 3
Do While ws.Cells(Rand, 3).Value <> "" And Rand < 65536
If Me.repereche.ListCount <> 0 Then
For i = 0 To (Me.repereche.ListCount)
If Me.repereche.List(i, 0) <> Mid(ws.Cells(Rand, 3).Value, 4, 10) Then
Me.Controls("repereche").AddItem Mid(ws.Cells(Rand, 3).Value, 4, 10)
End If
Next i
ElseIf Me.repereche.ListCount = 0 Then
Me.Controls("repereche").AddItem Mid(ws.Cells(Rand, 3).Value, 4, 10)
End If
Rand = Rand + 1
Loop
The problem with this code (and I don't know where the problem is?) is that whenever I change the page and I come back to the page where this combobox is... it adds more (not unique) and more items. Where am I wrong?
Try this code:
Dim ws As Worksheet
Dim rCell As Range
Set ws = Worksheets("BD_IR")
'//Clear combobox
repereche.Clear
With CreateObject("Scripting.Dictionary")
For Each rCell In ws.Range("C3", ws.Cells(Rows.Count, "C").End(xlUp))
If Not .exists(rCell.Value) Then
.Add rCell.Value, Nothing
End If
Next rCell
repereche.List = .keys
End With
I prefer this over a collection as you can check if the value exists in the dictionary rather than using on error and add the entire collection to the combobox at once.
这篇关于在组合框(VBA)中只有唯一的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!