Listing Data in a ListBox Challenge

Pinkster69

New Member
Joined
Jun 19, 2012
Messages
48
Hi Guys,

I have a form with 4 TextBoxe's "TexBox1,2,3 etc" and a ListBox1 were the whole idea is that you enter a value in TexBox1 and the VBA code looks for any and all items on your data "respective worksheet" and lists any/all data that it finds "corresponding to the value in TextBox 1" in the ListBox1, just like a database of sorts. Also note the other TextBoxe's 2,3,4 have info in them as well corresponding to the value you put in TextBox1 using the offset function.

Please note: The code I have WORKS for the condition above
but what I am having problems with is that I want it to work for all the TextBoxes respectively!

What I mean by this is that when ever I enter data in TextBox 1 or 2 or 3 or 4 that the results will be listed in the ListBox1. The reason I need this is, if the info I require for TextBox1 isn't available I can search for the info using the other criteria in the other TextBoxes.

I have attached the code I have already so please feel free to modify it. If you require the test worksheet please let me know and I will email it on to you, this might be better to give you an idea to see it working.

Really could use your help guys??

PS: forgot to mention that when the code finds entries that relate to TextBox1 it comes up with a message window with the number of entries it found... just in case you were wondering about the code at the bottom of the cmdFind method.



Code:
Dim MyData     As Range
Dim c          As Range
Dim rFound     As Range
Dim r          As Long
Dim rng        As Range


Dim oCtrl      As MSForms.Control




Private Sub cmbFind_Click()
    Dim strFind As String    'what to find
    Dim FirstAddress As String
    Dim rSearch As Range  'range to search
    Set rSearch = Sheet1.Range("a6", Range("a65536").End(xlUp))
    Dim f      As Integer


   
    strFind = Me.TextBox1.Value    'what to look for


    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            With Me    'load entry to form
                .TextBox2.Value = c.Offset(0, 1).Value
                .TextBox3.Value = c.Offset(0, 2).Value
                .TextBox4.Value = c.Offset(0, 3).Value
                'BBBBBBBBBBBBBBBBBBBBB
                
                f = 0
            End With
            FirstAddress = c.Address
            Do
                f = f + 1    'count number of matching records
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
            If f > 1 Then
                Select Case MsgBox("There are " & f & " instances of " & strFind, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")


                    Case vbOK
                        FindAll
                    Case vbCancel
                        'do nothing
                End Select
                


            End If
        Else: MsgBox strFind & " not listed"    'search failed
        End If
    End With
    If Sheet1.AutoFilterMode Then Sheet1.Range("A8").AutoFilter


End Sub


Sub FindAll()
    Dim strFind As String    'what to find
    Dim rFilter As Range     'range to search
    Set rFilter = Sheet1.Range("a8", Range("d65536").End(xlUp))
    Set rng = Sheet1.Range("a7", Range("a65536").End(xlUp))
    strFind = Me.TextBox1.Value
    With Sheet1
        If Not .AutoFilterMode Then .Range("A8").AutoFilter
        rFilter.AutoFilter Field:=1, Criteria1:=strFind
        Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
        Me.ListBox1.Clear
        For Each c In rng
            With Me.ListBox1
                .AddItem c.Value
                .List(.ListCount - 1, 1) = c.Offset(0, 1).Value
                .List(.ListCount - 1, 2) = c.Offset(0, 2).Value
                .List(.ListCount - 1, 3) = c.Offset(0, 3).Value
                .List(.ListCount - 1, 4) = c.Offset(0, 4).Value
            End With
        Next c
    End With
End Sub
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,169
Have you considered using the Autofilter feature? You would have dropdown lists in each column of the header row. You select the item you want to filter on from one or of the dropdown list and only those records would be shown.

Video: Filter data by using an AutoFilter


The Autofilter feature could be used in VBA to populate your Listbox instead of the .Find method. Or you may not need your UserForm as it duplicates what Autofilter does on the worksheet.
 
Last edited:

Pinkster69

New Member
Joined
Jun 19, 2012
Messages
48
Thanks so much for your reply,

Unfortunitly the AutoFilter is not what I was looking for! I am looking for VBA Code in addition to what I have attached for my userform to work with multiple Textboxes!
The code I attached is just a very small part of the overall large program I am trying to develop, Just stuck on figuring out how to search with multiple TextBoxes.

Thanks again for the effort though!

regards

Pinkster69
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,169
I would still recommend using Autofilter. Try something like this (I made a few assumptions).

Code:
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cmbFind_Click()


    [COLOR=darkblue]Dim[/COLOR] rSearch [COLOR=darkblue]As[/COLOR] Range        [COLOR=green]'range to search[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] rSearch = Sheet1.Range("A5", Range("A65536").End(xlUp)).Resize(, 5)
    
    rSearch.AutoFilter    [COLOR=green]'Clear previous filter if any[/COLOR]
    Me.ListBox1.Clear
    
    [COLOR=darkblue]If[/COLOR] Len(TextBox1.Value) [COLOR=darkblue]Then[/COLOR] rSearch.AutoFilter Field:=1, Criteria1:=TextBox1.Value
    [COLOR=darkblue]If[/COLOR] Len(TextBox2.Value) [COLOR=darkblue]Then[/COLOR] rSearch.AutoFilter Field:=2, Criteria1:=TextBox2.Value
    [COLOR=darkblue]If[/COLOR] Len(TextBox3.Value) [COLOR=darkblue]Then[/COLOR] rSearch.AutoFilter Field:=3, Criteria1:=TextBox3.Value
    [COLOR=darkblue]If[/COLOR] Len(TextBox4.Value) [COLOR=darkblue]Then[/COLOR] rSearch.AutoFilter Field:=4, Criteria1:=TextBox4.Value
    
    [COLOR=darkblue]If[/COLOR] Sheet1.Range("A65536").End(xlUp).Row > 5 [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]With[/COLOR] Me.ListBox1
            .Clear
            [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] c [COLOR=darkblue]In[/COLOR] rSearch.Offset(1).Resize(, 1).SpecialCells(xlCellTypeVisible)
                .AddItem c.Value
                .List(.ListCount - 1, 1) = c.Offset(0, 1).Value
                .List(.ListCount - 1, 2) = c.Offset(0, 2).Value
                .List(.ListCount - 1, 3) = c.Offset(0, 3).Value
                .List(.ListCount - 1, 4) = c.Offset(0, 4).Value
            [COLOR=darkblue]Next[/COLOR] c
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
            MsgBox "There are " & rSearch.Resize(, 1).SpecialCells(xlCellTypeVisible).Count - 1 & _
                   " records found. ", vbExclamation, "Multiple entries"
            
    [COLOR=darkblue]Else[/COLOR]
        [COLOR=darkblue]If[/COLOR] MsgBox("No matching records. " & vbCr & "Clear the search fields? ", vbYesNo, "No Records Found") = vbYes [COLOR=darkblue]Then[/COLOR]   [COLOR=green]'search failed[/COLOR]
            TextBox1.Value = ""
            TextBox2.Value = ""
            TextBox3.Value = ""
            TextBox4.Value = ""
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    Sheet1.AutoFilterMode = [COLOR=darkblue]False[/COLOR]


[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 

Pinkster69

New Member
Joined
Jun 19, 2012
Messages
48
Sorry for the delay in getting back to you!
Thanks a mill for the code, I will try it out and let you know how I get on!
 

Pinkster69

New Member
Joined
Jun 19, 2012
Messages
48
Hey AlphaFrog,

Just retrofitting your code into mine, super job Man! It worked a dream!
Thanks for all the help! You always come up trumps for me.

Thanks again!

PS: is there a rating system on MrExcel, if so you have 5 stars from me boyo!!
:pray:
 

Forum statistics

Threads
1,082,574
Messages
5,366,387
Members
400,887
Latest member
tporeda

Some videos you may like

This Week's Hot Topics

Top