VBA用户窗体：如何防止ListBox_variante向Listbox_seleccion添加重复项？

要实现双击listbox_variante项添加到listbox_seleccion且避免重复，核心是在添加前检查目标ListBox是否已存在该项，以下是两种可行的修改方案：

方案一：直接在双击事件中加入重复检查 #

修改原listbox_variante_DblClick过程，添加内层循环验证重复：

Private Sub listbox_variante_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim x As Integer
    Dim itemToAdd As String
    Dim isDuplicate As Boolean
    Dim y As Integer
    
    For x = 0 To listbox_variante.ListCount - 1
        If listbox_variante.Selected(x) = True Then
            itemToAdd = listbox_variante.List(x)
            isDuplicate = False
            
            ' 遍历目标ListBox，检查是否已存在当前项
            For y = 0 To listbox_seleccion.ListCount - 1
                If listbox_seleccion.List(y) = itemToAdd Then
                    isDuplicate = True
                    Exit For ' 找到重复项后立即终止检查
                End If
            Next y
            
            ' 仅当无重复时才添加
            If Not isDuplicate Then
                listbox_seleccion.AddItem itemToAdd
            End If
        End If
    Next x
End Sub


Private Sub UserForm_Initialize()
    listbox_variante.RowSource = "Tabla2[DESCRIPCIÓN]"
End Sub

方案二：封装重复检查为辅助函数（代码更易复用） #

把重复检查逻辑抽成独立函数，让主代码更简洁，后续其他场景也能复用该函数：

' 辅助函数：判断指定ListBox中是否存在目标项
Private Function ItemExists(targetListBox As MSForms.ListBox, item As String) As Boolean
    Dim i As Integer
    For i = 0 To targetListBox.ListCount - 1
        If targetListBox.List(i) = item Then
            ItemExists = True
            Exit Function
        End If
    Next i
    ItemExists = False
End Function

Private Sub listbox_variante_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim x As Integer
    Dim itemToAdd As String
    
    For x = 0 To listbox_variante.ListCount - 1
        If listbox_variante.Selected(x) = True Then
            itemToAdd = listbox_variante.List(x)
            ' 调用辅助函数判断，无重复则添加
            If Not ItemExists(listbox_seleccion, itemToAdd) Then
                listbox_seleccion.AddItem itemToAdd
            End If
        End If
    Next x
End Sub


Private Sub UserForm_Initialize()
    listbox_variante.RowSource = "Tabla2[DESCRIPCIÓN]"
End Sub

核心逻辑说明 #

两种方案的本质都是：

1. 获取当前选中的要添加的内容
2. 遍历listbox_seleccion的所有项，对比是否与要添加的内容一致
3. 仅当内容不存在时，执行AddItem操作

内容的提问来源于stack exchange，提问作者Lucas Gonzalo Nicolini