Compare first column of Two listboxs to avoid duplicate values

[kapela2017]

Greetings and Merry Christmas to all the members of the community, I am currently trying to create (UNSUCCESSFULLY) two listboxes, (listbox1 and listbox2) the first one through the doubleclick event and taking as reference an ID number transfers the selected data to listbox2, which What I would like is to compare the ID in column 1 of both listboxes and avoid duplicate values, if someone can guide me with the syntax I would appreciate it, successes and blessings---

 

[Micron]

You don't say where the list items for listbox1 come from so this may not apply. If you move (not copy) the items over then you cannot have duplicates since that which was moved is no longer available in listbox1. So that would be simpler than looping over the list to see if the value you want to copy over is already there? That won't apply if the list items comes from a range of cells on a worksheet.

You could also post the code you tried to provide more insight. If you do, please use code tags (vba icon on posting toolbar) to maintain indentation and readability.

 

[kapela2017]

You don't say where the list items for listbox1 come from so this may not apply. If you move (not copy) the items over then you cannot have duplicates since that which was moved is no longer available in listbox1. So that would be simpler than looping over the list to see if the value you want to copy over is already there? That won't apply if the list items comes from a range of cells on a worksheet.

You could also post the code you tried to provide more insight. If you do, please use code tags (vba icon on posting toolbar) to maintain indentation and readability.

Thank you senior, I will try to provide you with as much information as possible, the values come from a table on sheet 3, since I don't know how to compare both listboxes, I linked listbox 3 to a table, in this way I compare listbox1 with the table , and if the value is duplicated, it won't let me pass them to a set of textboxes where I perform some options to later pass them to listbox3(merit for him User mmhill that gave me these ideas for the code...), with their new values, I need to compare both listboxes and not use the support table (tbl) , thanks for taking a look. time to review my case I will be attentive to any ideas ...

[CODE=vba]
Private Sub Tbx_BuscarProducto_Change()
Dim items As Variant
Dim i As Long

Application.ScreenUpdating = False
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect
Next ws

    ListBox1.Clear
    
  'Here I load the listbox1 with the values of the range, it has a total of 6 columns...
  'The range is a table found on sheet 3..
    
    items = Range("Guia").CurrentRegion.Rows.Count
        For i = 2 To items
            If LCase(Cells(i, 1).Value) Like "*" & LCase(Me.Tbx_BuscarProducto.Value) & "*" Then
            
   Me.ListBox1.AddItem Cells(i, 1)
                Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = Cells(i, 2)
                Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = Cells(i, 3)
                 Me.ListBox1.List(Me.ListBox1.ListCount - 1, 3) = Cells(i, 4) & "$"
                  Me.ListBox1.List(Me.ListBox1.ListCount - 1, 4) = Cells(i, 5) & "$"
                   Me.ListBox1.List(Me.ListBox1.ListCount - 1, 5) = Cells(i, 6) & "Bs"
                  
                  ElseIf LCase(Cells(i, 2).Value) Like "*" & LCase(Me.Tbx_BuscarProducto.Value) & "*" Then
            
             Me.ListBox1.AddItem Cells(i, 1)
                Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = Cells(i, 2)
                Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = Cells(i, 3)
                 Me.ListBox1.List(Me.ListBox1.ListCount - 1, 3) = Cells(i, 4) & "$"
                  Me.ListBox1.List(Me.ListBox1.ListCount - 1, 4) = Cells(i, 5) & "$"
                  Me.ListBox1.List(Me.ListBox1.ListCount - 1, 5) = Cells(i, 6) & "Bs"
                  
                  
                  End If
     
    Next i
   
For Each ws In ActiveWorkbook.Worksheets
ws.Protect
Next ws
    
End Sub

[/CODE]


This is where I am stuck I need to compare both textboxes according to Id..

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'I need it to be through this event

Application.ScreenUpdating = False

 Dim ws As Worksheet
 For Each ws In ActiveWorkbook.Worksheets
 ws.Unprotect
 Next ws
 Dim oDBR As Range, valList, m
 Dim tbl As ListObject
 Dim NewRow As ListRow
 Dim i As Long
 Dim codigo As Variant
 Set tbl = Sheets("Facturacion").ListObjects("Factura")
 Set oDBR = Sheets("Facturacion").ListObjects("Factura").ListColumns("CODIGO").DataBodyRange
     Sheets("Facturacion").Activate
     
     
    'Here I verify that the number of rows in the destination table is greater than zero to avoid comparison errors
  If tbl.ListRows.Count > 0 Then
   'here I establish a range within listbox 1
    valList = ListBox1.Value
    'convert the selected item to number
    If IsNumeric(valList) Then valList = CLng(valList)
    m = Application.Match(valList, oDBR, 0)
   
   
    If Not IsError(m) Then
        MsgBox "ESTE PRODUCTO YA SE ENCUENTRA EN LA FACTURA POR FAVOR VERIFIQUE SU PESO"
        
        
        Range("L4:L5") = ListBox1.List(i, 0)
    Exit Sub
    
    End If
     End If
    
'I clean the texboxes of old values

 Me.TextCodigo.Value = ""
Me.TextArticulo.Text = ""
Me.TextCantidad.Value = ""
Me.TextCalculoD.Value = ""
Me.TextCalculoB.Value = ""
TextCantidad.Value = ""
TextValor.Value = ""



 If tbl.ListRows.Count >= 0 Then
 
 
  With ListBox1
  
  'here the code It allows only if the value is not in the destination table to add values to the textboxes _
  (It is necessary since I perform some calculations in them to later add them to listbox 3)
  
    For i = 0 To .ListCount - 1
    If OptionButton2.Value = True And _
       ListBox1.Selected(i) Then
        Me.TextValor.Value = .List(i, 4)
        Me.TextCantidad.Value = 1
        Me.TextCodigo.Value = .List(i, 0)
        Me.TextArticulo.Text = .List(i, 1)
       
       
        
        End If
        
        
        If OptionButton1.Value = True And _
       .Selected(i) Then
       Me.TextValor.Value = .List(i, 3)
       Me.TextCantidad.Value = 1
         Me.TextCodigo.Value = .List(i, 0)
        Me.TextArticulo.Text = .List(i, 1)
      
        
           .Selected(i) = False
        
        
      End If
      
      
      
    Next
End With

End If

'Here I update the listbox3 to show me the values of the table, _
it 's what I want to avoid, I use it only as a support because I don't know how to compare 2 listboxes..

 Call ListBox3_Change
 
 Tbx_BuscarProducto.Value = ""
 
 Tbx_BuscarProducto.SetFocus
 
  OptionButton2.Value = True
  
   ListBox1.Clear
   


For Each ws In ActiveWorkbook.Worksheets
ws.Protect
Next ws

Application.ScreenUpdating = True

    

End Sub