List Boxes and Excel

[EliseFreedman]

Hi There

I have an application which potentially contains a lot of data. The application will be used by several employees who do not have much excel knowledge. Therefore I am trying to make the application as simple as possible

I have been asked by the users if they can have a form for searching the data for matching records. I have managed to do this successfully using a form with a searchbox and a listbox into which i load the matching records. My problem is that obviously each time the user searches, there will be a varying number of matches. I am finding that each time I do a search, the previous contents of the listbox are not being cleared out.

I tried me.listbox1.clear and also Me.ListBox1.List() = "" each time the contents of the textbox changed but neither worked.

Does anyone have any ideas


The code is as follows

Private Sub cmbFindAll_Click()
    
    
    Dim FirstAddress As String
    Dim strFind As String    'what to find
    Dim rSearch As Range     'range to search
    Dim fndA, fndB, fndC, fndD, fndE, FndF, fndG As String
    Dim head1, head2, head3, head4, head5, head6, head7   As String    'headings for list
    Dim i As Integer
    Dim F As Integer
    i = 1
    Set rSearch = Sheet1.Range("a2", Range("a65536").End(xlUp))
    strFind = Me.TextBox1.Value
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            'load the headings
            head1 = Range("a1").Value
            head2 = Range("b1").Value
            head3 = Range("c1").Value
            head4 = "Visit Date"
            head5 = "Injury Cause"
            head6 = "How Referred"
            head7 = "Area of Pain"
            F = 0
            With Me.ListBox1
                MyArray(0, 0) = head1
                MyArray(0, 1) = head2
                MyArray(0, 2) = head3
                MyArray(0, 3) = head4
                MyArray(0, 4) = head5
                MyArray(0, 5) = head6
                MyArray(0, 6) = head7
            End With
            FirstAddress = c.Address
            Do
                'Load details into Listbox
                fndA = c.Value
                fndB = c.Offset(0, 1).Value
                fndC = c.Offset(0, 2).Value
                fndD = c.Offset(0, 8).Value
                fndE = c.Offset(0, 9).Value
                FndF = c.Offset(0, 10).Value
                fndG = c.Offset(0, 14).Value

                MyArray(i, 0) = fndA
                MyArray(i, 1) = fndB
                MyArray(i, 2) = fndC
                MyArray(i, 3) = fndD
                MyArray(i, 4) = fndE
                MyArray(i, 5) = FndF
                MyArray(i, 6) = fndG
                
                
                F = F + 1
                i = i + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
            If i > 1 Then
                MsgBox "There are " & F & " instances of " & strFind
                Me.Height = 318
            End If
            Else: MsgBox strFind & " Not Listed"
        End If
    End With
    'Load data into LISTBOX
        Me.ListBox1.List() = MyArray

End Sub

 

[WinblowsME]

Try Me.ListBox1.cls

 

[bdmangum]

Here's something you could try. I only set it up to work for MyArray(0,i), but with a little tweaking you could get it to work. This may or may not do what you need.

C = 0

While ListBox1.List(0, C) <> ""
    C = C + 1
    On Error GoTo ErrorHandler
Wend

ErrorHandler:

C = C - 1

For A = C To 0 Step -1
    Me.ListBox1.List(0, A) = ""
Next

 

[combo]

Me.Listbox1.Clear should clear the list.
Test the listbox you reference to, maybe the userform requires repainting (Me.Repaint).

combo