Prevent Duplicate Entries in Single Cells from UserForm with Embedded ListBox - time sensitive

[aashah3]

Hi All,

I have scourged the internet to no avail. I am trying to do something that seems very simple: I am using a multiple selection listbox which is embedded into a UserForm. (It is a check-all-that-apply form linked to a DV list).

All I would like to accomplish is a way to prevent duplicate entries from this UserForm into any single cell.

I found a great code from Contextures that does exactly what I would like (ignores duplicate entries), however it doesn't apply to a UserForm listbox.

Could somebody guide me in editing the code so that it will work with my UserForm?
Also where would I paste the code so that it will be effective? In the coding for the UserForm or the General Worksheet?

Here is the contextures code I found:

Option Explicit' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
If Target.Count > 1 Then GoTo exitHandler


On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler


If rngDV Is Nothing Then GoTo exitHandler


If Intersect(Target, rngDV) Is Nothing Then
   'do nothing
Else
  Application.EnableEvents = False
  newVal = Target.Value
  Application.Undo
  oldVal = Target.Value
  Target.Value = newVal
  If Target.Column = 3 Then
    If oldVal = "" Then
      'do nothing
      Else
      If newVal = "" Then
      'do nothing
      Else
        lUsed = InStr(1, oldVal, newVal)
        If lUsed > 0 Then
            Target.Value = oldVal
        Else
            Target.Value = oldVal _
              & ", " & newVal
        End If
        
      End If
    End If
  End If
End If


exitHandler:
  Application.EnableEvents = True
End Sub

Here is the code for my UserForm:

Option Explicit


Private Sub cmdClose_Click()
  Unload Me
End Sub


Private Sub cmdOK_Click()
Dim strSelItems As String
Dim lCountList As Long
Dim strSep As String
Dim strAdd As String
Dim bDup As Boolean


On Error Resume Next
strSep = ", "


With Me.lstDV
   For lCountList = 0 To .ListCount - 1
      
      If .Selected(lCountList) Then
         strAdd = .List(lCountList)
      Else
         strAdd = ""
      End If
      
      If strSelItems = "" Then
         strSelItems = strAdd
      Else
         If strAdd <> "" Then
            strSelItems = strSelItems & strSep & strAdd
         End If
      End If
   
   Next lCountList
End With


With ActiveCell
   If .Value <> "" Then
      .Value = ActiveCell.Value & strSep & strSelItems
   Else
      .Value = strSelItems
   End If
End With


Unload Me


End Sub




Private Sub lstDV_Click()


End Sub


Private Sub UserForm_Initialize()
Me.lstDV.RowSource = strDVList
End Sub

I apologize if I come across as ignorant, as I really don't know a lot of VBA, but your help is greatly appreciated.

Thanks,
Asha