Consulting

Results 1 to 3 of 3

Thread: Ensure Selected Item in Listbox does not exist in Range

  1. #1

    Ensure Selected Item in Listbox does not exist in Range

    I have a userform with a listbox that allows me to select multiple items. The selected items (only the first column is exported; the second column is for display only) are placed in column K. Now my problem is how to I ensure that the items that I select is not a duplicate of a value already found on column K? Anyone got ideas?


    Private Sub CommandButton1_Click()
    
    
      Dim lngItem As Long
    
    
        For lngItem = 0 To ListBox1.ListCount - 1
        
            If ListBox1.Selected(lngItem) Then
                With Sheets("SAMPLING")
                    .Cells(.Rows.Count, "K").End(xlUp).Offset(1, 0).Value = ListBox1.List(lngItem, 0)
                End With
            End If
        
        Next lngItem
        
    End Sub

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Private Sub CommandButton1_Click()
    Dim lngItem As Long
    Dim strArg As String
    Dim rngFound As Range
    
    
        For lngItem = 0 To ListBox1.ListCount - 1
            If ListBox1.Selected(lngItem) Then
                 strArg = ListBox1.Selected(lngItem)
                 With Sheets("SAMPLING")
                      Set rngFound = .Range("K:K").Find strArg
                      If rngFound is Nothing then _
                    .Cells(.Rows.Count, "K").End(xlUp).Offset(1, 0).Value = strArg
                End With
            End If
        Next lngItem
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •