Multi-Select List Box, Distinct Values (2 Viewers)

[MattBaldry]

Hi Forum Minds,

I have found the below code on here which works almost perfectly.

Display Selection of a List Box in a Text Box

    Dim strSelected As String
    Dim varItem As Variant

    With Me.lstLiveWorksOrderList
        For Each varItem In .ItemsSelected
            strSelected = strSelected & ", " & .Column(2, varItem)
        Next varItem
        Me.txtSelectedCustomerOrders = Mid(strSelected, 2)
    End With

How would I be able to change this to only show unique/distinct values?

~Matt

 

[Mark_]

Easiest way is to make sure your source is distinct. Not sure what you are really trying to do with this as it looks like you are parsing out part of field after selecting. Is that something you could break out before creating your list?

 

[DHookom]

I would use Instr() to determine if the item already exists. There may be an issue with items that end with the same letters.

    Dim strSelected As String
    Dim varItem As Variant

    With Me.lstLiveWorksOrderList
        For Each varItem In .ItemsSelected
            If Instr(strSelected, .Column(2,varItem) & ",") = 0 Then
                strSelected = strSelected & ", " & .Column(2, varItem)
            End If
        Next varItem
        Me.txtSelectedCustomerOrders = Mid(strSelected, 2)
    End With

 

[MattBaldry]

Easiest way is to make sure your source is distinct. Not sure what you are really trying to do with this as it looks like you are parsing out part of field after selecting. Is that something you could break out before creating your list?

Hi Mark,

On this occasion I cannot make this column distinct as the main column has different values.

Please see below for some sample data.

The information I need to pass to our customer is their order number and their part numbers. The Works Order numbers are for our own internal reference.

Hopefully this makes sense.

~Matt

 

[MajP]

   Dim strSelected As String
    Dim varItem As Variant
    Dim aItems() As String
    Dim newItem As String
    Dim i As Integer
    Dim found As Boolean
    With Me.lstLiveWorksOrderList
        For Each varItem In .ItemsSelected
            newItem = .Column(2, varItem)
            aItems = Split(strSelected, ", ")
            found = False
            For i = 0 To UBound(aItems)
               If Trim(aItems(i)) = Trim(newItem) Then found = True
               Exit For
            Next i
            If Not found Then
              If strSelected = "" Then
                strSelected = newItem
              Else
                strSelected = strSelected & ", " & newItem
             End If
            End If
        Next varItem
        Me.txtSelectedCustomerOrders = strSelected
    End With
End Sub

You could probably could simply use instr instead of the split and loop. But that may cause problem if you have substrings ( ex. 18931 and 189)