Sorting And Adding to a Dropdown List

graham80

New Member
Joined
Jul 1, 2007
Messages
6
I've been dancing around this problem for weeks now with no idea how to solve it...

I would like to add names that are in another workbook into a dropdown list...the only problem is that they need to be sorted alphabetically first but only in the dropdown and not in the worksheet...

So all i need to know is how to reference the dropdown list to .addItem? And is there someway to sort the items listed in the dropdown? Also, how can I give my dropdown a title that shows when it is idle?

Many thanks!!!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Use a label and a timer for the idle heading.

You didn't state but I assume this on a Userform; listboxes may also appear directly on the spreadsheet. I'm not surprised to see no replies to what would seem to be a quite common and simple operation as I have never been able to locate much information on sorting a form listbox.

Here's what I know. You can load the values to a hidden or temporary worksheet and sort the range, or roll your own sort, such as
Code:
Private Sub SortMe()
  Dim i As Long, sMyArray() As String, vSelected, vDesired, rng As Range
  On Error GoTo err1cmdSortMe_Click
  ReDim sMyArray(1 To lstBox1.ListCount)
  cmdSortMe.Enabled = False
  For i = lstBox1.ListCount - 1 To 0 Step -1
      sMyArray(i + 1) = lstBox1.List(i)
      If i = lstBox1.ListIndex Then vSelected = sMyArray(i + 1)
  Next i
  Call BubbleSortNonproper(sMyArray)
  For i = lstBox1.ListCount - 1 To 0 Step -1
      lstBox1.List(i) = sMyArray(i + 1)
      If sMyArray(i + 1) = vSelected Then vDesired = i
  Next i
  lstBox1.ListIndex = vDesired
err1cmdSortMe_Click:  'from sorting zero items
End Sub
Sub BubbleSortNonproper(List() As String) 'Sort List array ascending order
    Dim First As Long, Last As Long
    Dim i As Long, j As Long, sTemp As String
   
    First = LBound(List)
    Last = UBound(List)
    For i = First To Last - 1
        For j = i + 1 To Last
            If LCase(List(i)) > LCase(List(j)) Then
                sTemp = List(j)
                List(j) = List(i)
                List(i) = sTemp
            End If
        Next j
    Next i
End Sub
While this is easy to follow, faster sort routines are available.
 
Upvote 0
Yes...The dropdown is directly on the worksheet..
Sorry for not specifying! This is part of the reason why I don't know how to reference it to add items into it...
 
Upvote 0
While you can .AddItem to a worksheet listbox, instead the normal procedure is to "point" it to a range. I suggest a named range. Put the name or the range addresses in the ListFillRange property.

In this case you don't need the label. You can set the property ColumnHeads=true and the top value of the range will show in the heading. You can stick "Idle" or whatever in that value when desired. Note that some XL versions have a bug with column headings, so you may or may not want to include the "idle indicator" cell in the range, vs. right before the range. Experiment. (Indicating your Excel version is recommended for questions.)

You still need the timer. See OnTime in VBA help/example code.

Now you don't need to sort an array. Just sort your range a la rng.sort.

All of this refers to working with a copy of the other workbook's data. You might want to work with it in a hidden sheet or with display updates toggled off, as a matter of choice or application requirements or manipulative user repression.
 
Upvote 0
Yes. I actually used the phrase "manipulative user repression." Disgusting, huh? Well - oops,sorry! Got to go! Microsoft is on the phone, offering me a position as Vice-President of Interface Repression.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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
Back
Top