[RESOLVED] Excel 2003 - Find cell value match in sheet Listbox

[SQLADOman]

First off, the code below is working for finding and selecting the value in a Listbox that matches the value in Cell A1. However I pieced this together through trial and error so I am not sure whether or not I did it in an unorthodox or sloppy manner.

So my first question is my code ok?

Second question is how difficult would it be to sort the items in the Listbox?
-- if it requires API's at all then I'd prefer to skip it, but I was thinking it probably can be done in a straight forward manner.

I've attached a sample workbook

Thanks

Frank

Edit: I do realize that I should encase most of it between With and end with, I'll do that later

Option Explicit

Private Sub CommandButton1_Click()
      Dim i As Integer
      
      For i = 1 To ListBox1.ListCount
          
          ListBox1.Selected(i - 1) = True
          
          If UCase(ListBox1.Value) = UCase(Range("A1").Value) Then
              Exit For
          End If

      Next i
End Sub

Private Sub CommandButton2_Click()

With Me.ListBox1
    .Clear
    .AddItem "pear"
    .AddItem "orange"
    .AddItem "three"
    .AddItem "GRAPE"
    .AddItem "Peach"
End With

End Sub

[Siddharth Rout]

Yes the code is Ok.

To sort the items in a listbox, use this

First of all create a sheet and name it "Temp" and then hide that sheet. Once that is done replace all your code above with this...

Option Explicit

Dim I As Long, J As Long
Dim lastRow As Long

Private Sub CommandButton1_Click()
    For I = 1 To ListBox1.ListCount
        ListBox1.Selected(I - 1) = True
        If UCase(ListBox1.Value) = UCase(Range("A1").Value) Then
            Exit For
        End If
    Next I
End Sub

Private Sub CommandButton2_Click()
    With Me.ListBox1
        .Clear
        .AddItem "pear"
        .AddItem "orange"
        .AddItem "Three"
        .AddItem "GRAPE"
        .AddItem "Peach"
    End With
    
    For I = 0 To ListBox1.ListCount - 1
        Sheets("Temp").Range("A" & I + 1) = ListBox1.List(I)
    Next
    
    lastRow = Sheets("Temp").Range("A" & Rows.Count).End(xlUp).Row
    
    Sheets("Temp").Range("A1:A" & lastRow).Sort Key1:=Sheets("Temp").Range("A1"), Order1:=xlAscending, _
    Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    
    ListBox1.Clear
    
    For I = 1 To lastRow
        ListBox1.AddItem Sheets("Temp").Range("A" & I)
    Next
    
    Sheets("Temp").Columns("A:A").ClearContents
End Sub

Hope this helps...

Sid

[westconn1]

you can use any sorting alogrithym to sort an array, from the contents of listbox, but different contents may need different handling
numeric values are not good when sorted as strings, like 22 will come before 7 etc
sorting values in a sheet can work fine, but possibly comes under the heading of unorthodox, not to provide your own code
as listboxes contain limited number of items, in most cases a bubblesort would be quite adequate, but for larger number of items quick sort, or other more advanced sorting would be advantageous

you do not need this line in your code and it has no adavantage

ListBox1.Selected(i - 1) = True

or if you want to finish with the item selected, move to immediately before exit for, so it is only called the one time

[SQLADOman]

Thanks Koolsid for the code and thanks Pete for the tips

Now that I went to use real data in the Listbox I found that some times the Customer name or a portion of it is also contained within the address of a different customer, so depending on what order it is within the list the selection is some times incorrect

In other words I need to search the customer name and select it only if a first word match is found in the Listbox.

and if you could show me how to dump the list items into an array and sort it I would enjoy learning how to do that. I do already know how to reload the array back into the Listbox.

Thanks

Edit:#2
Customer List
Harvey
Reach
ati
sac
BENCO

Private Sub CommandButton2_Click()

With Me.ListBox1
    .Clear
    .AddItem "Harvey Inc 1703 Sacramento Way BUENA PARK,CA. 90599"
    .AddItem "Benco 1222 Reach Circle Anaheim CA 92586"
    .AddItem "REACH INTERNATIONAL 6900 ORANGETHORPE AVE San Diego,CA. 90620"
    .AddItem "SAC COMPANY 122 Bencotton Dr Fulerton,CA. 90591"
    .AddItem "ATI LTD Inc 1703 Harvey Street BUENA PARK,CA. 90599"
End With

End Sub

[westconn1]

try like

If UCase(Left(ListBox1.Value, InStr(ListBox1.Value, " ") - 1)) = UCase(ActiveCell.Value) Then

[SQLADOman]

Thanks Pete.

Works like a charm