Populate list box in order

ccordner

Active Member
Joined
Apr 28, 2010
Messages
355
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:

VBA Code:
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:

VBA Code:
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:

VBA Code:
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,810
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Active Member
Joined
Apr 28, 2010
Messages
355
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:

VBA Code:
If .ListCount = 0 Then

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

    .AddItem strData
    
Else

Originally, it was just:

VBA Code:
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

Well-known Member
Joined
May 16, 2017
Messages
1,810
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Why are you using the ">" operator to qualify whether or not a new item gets added to the listbox?
 

ccordner

Active Member
Joined
Apr 28, 2010
Messages
355
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

Well-known Member
Joined
May 16, 2017
Messages
1,810
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,176,393
Messages
5,902,802
Members
434,997
Latest member
bigolbearking

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top