组合框中仅包含唯一记录 (VBA)
我有一个组合框,我可以在其中添加 Excel 工作表中的一些内容和一堆内容。我只想要唯一的记录,并且希望在切换到此页面时更新它们。 为此,我使用了以下代码:
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
此代码的问题(我不知道问题出在哪里?)是每当我更改页面并返回到此组合框所在的页面时......它会添加更多内容(不是唯一的)和更多项目。我哪里错了?
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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
试试这个代码:
我更喜欢这个而不是集合,因为您可以检查字典中是否存在该值,而不是在错误时使用并立即将整个集合添加到组合框。
Try this code:
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.