Populate list box in order

[ccordner]

I'm building a form which involves populating list boxes, where the user can select from a list of values in box A (lstLocations) to transfer to list B (lstMainLocs).

I'm using the code below:

Private Sub txtDiagrams_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

'Split the pasted data into an array, one line for each diagram
arrDiagrams() = Split(txtDiagrams.Text, vbCrLf)

'Populate the list box with the available locations
With lstLocations

    'Clear the list box ready for new data
    .Clear
    
    'Get the location for each diagram
    For lngCounter = 0 To UBound(arrDiagrams()) - 1
    
        arrLine = Split(arrDiagrams(lngCounter), vbTab)
        strLocName = arrLine(1)
        Call AddEntry("lstLocations", strLocName)
        
    Next
    
    'Select the first item
    .ListIndex = 0

End With

End Sub

The data pasted into txtDiagrams is essentially CSV format. The function it refers to is this:

Function AddEntry(lstName As String, strData As String)

Set lstBox = Me.Controls(lstName)

With lstBox

If .ListCount = 0 Then

    .AddItem strData
    
ElseIf strData > .List(.ListCount - 1) Then

    .AddItem strData
    
Else

    For lngLocalCount = 0 To .ListCount - 1

        If strData < .List(lngLocalCount) Then

            .AddItem strData, lngLocalCount
            Exit For

        End If

    Next


End If

End With

End Function

The main problem is when the function is called by the sub 'txtDiagrams_keyUp', it will add duplicate values to the listbox, but when the same function is called by the following sub, it works perfectly:

Private Sub cmdAddMain_Click()

With lstLocations

    For lngListCount = 0 To .ListCount - 1
    
        If .Selected(lngListCount) = True Then
        
            Call AddEntry("lstMainLocs", .List(lngListCount))
        
        End If
        
    Next

End With

lstLocations.ListIndex = -1

End Sub

Any ideas why?

Thanks
Chris

 

[rlv01]

Have you considered modifying AddEntry to first check to see if strData is already in the listbox, and skip the .additem operation if it is?

 

[ccordner]

Good afternoon

Thanks for replying.

I have thought about doing that, and that's my backup plan. I'd just like to work out why it works on one listbox and not another. Similarly, I have had trouble with this bit:

If .ListCount = 0 Then

    .AddItem strData
    
ElseIf strData > .List(.ListCount - 1) Then

    .AddItem strData
    
Else

Originally, it was just:

If .ListCount = 0 or strData > .List(.ListCount - 1) Then

But again, it worked perfectly when called from one sub, but not the other! I can bodge round it, it's just frustrating that one piece of code works perfectly when called from one place, but not when called from another!

Thanks
Chris

 

[rlv01]

Why are you using the ">" operator to qualify whether or not a new item gets added to the listbox?

 

[ccordner]

Why are you using the ">" operator to qualify whether or not a new item gets added to the listbox?

What I was intending to do was say if the list is empty, or if the string goes at the end of the list, just add it. If not, loop through to find out where it goes. However, if there's a neater way of doing it, I'm very open to suggestions.

It's one of those silly things I need to do often, and I always have to look it up, or find a code snippet to reuse!

 

[rlv01]

If it were me, I would write a private function called something like 'SortList' , the only purpose of which is to sort the listbox's list array. Then just call it from AddEntry every time you add an item. You could also combine it with a duplicates check. Then you have a std function and don't have to reinvent the wheel each time you have to deal with a listbox.