Type in TextBox to show values in ListBox advice

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
Morning,
I have UserForm1 of which consists of TextBox1 & ListBox1

I wish to start typing in TextBox1 & the matching items are then shown in ListBox1

Some info for you.
The database values are in the same workbook & are on worksheet INFO
They are in Table 24 or the range is CR2:CR86 but values will be added in time making it then CR87, CR88, CR89 etc etc

I have started with the below but then got lost / confused

Rich (BB code):
    Private Sub TextBox1_Change()
      Dim r As Range, f As Range, cell As String, added As Boolean
      Dim sh As Worksheet
      
      Set sh = Sheets("INFO")
      sh.Select
      With ListBox1
        .Clear
        .ColumnCount = 3
        .ColumnWidths = "280;150;70"
        If TextBox1.Value = "" Then Exit Sub
        Set r = Range("CR2", Range("CR" & Rows.Count).End(xlUp))
        Set f = r.Find(TextBox1.Value, LookIn:=xlValues, LookAt:=xlPart)
        If Not f Is Nothing Then
          cell = f.Address
          Do
            added = False
            For i = 0 To .ListCount - 1
              Select Case StrComp(.List(i), f.Value, vbTextCompare)
                Case 0, 1
                  .AddItem f.Value, i                 'col B
                  .List(i, 1) = f.Offset(, -1).Value  'col A
                  added = True
                  Exit For
              End Select
            Next
            If added = False Then
              .AddItem f.Value                                 'col B
              .List(.ListCount - 1, 1) = f.Offset(, -1).Value  'col A
            End If
            Set f = r.FindNext(f)
          Loop While Not f Is Nothing And f.Address <> cell
          TextBox1 = UCase(TextBox1)
          .TopIndex = 0
        Else
          MsgBox "NO NUMBER WAS FOUND USING THAT INFORMATION", vbCritical, "HONDA PART NUMBER SEARCH"
          TextBox1.Value = ""
          TextBox1.SetFocus
        End If
      End With
    End Sub
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
The issue i have is,
When i type in the TextBox im taken to the worksheet INFO but there is no need so i must stay on the worksheet of which is HONDA SHEET
I select an item in the ListBox but get a run time error as shown in attached photo
 

Attachments

  • 585.jpg
    585.jpg
    33.7 KB · Views: 7
  • 586.jpg
    586.jpg
    35.8 KB · Views: 7
Upvote 0
Avoid using additem. Build your list and assign it in one go. I'm in a rush and will get back to you later on.
Here is an example file: See Filter listbox as you type.xlsm in this folder


Userform with TextBox1 and ListBox1, single column table "MyTable":
VBA Code:
Option Explicit

Private myList() As Variant

Private Sub TextBox1_Change()
    If Len(TextBox1) > 1 Then
        ListBox1.Visible = True
        ListBox1.List = GetCutList()
    Else
        ListBox1.Visible = False
    End If
End Sub

Private Sub UserForm_Initialize()
    myList = Range("MyTable")
    ListBox1.List = myList
End Sub

Private Function GetCutList() As Variant()
    Dim i As Long
    Dim ret() As Variant
    Dim ret2() As Variant
    Dim x As Long
   
    ReDim ret(UBound(myList, 1), 0)
    For i = 1 To UBound(myList, 1)
        If myList(i, 1) Like "*" & TextBox1 & "*" Then
            ret(x, 0) = myList(i, 1)
            x = x + 1
        End If
    Next
   
    If x > 0 Then
        ReDim ret2(x - 1, 0)
        For i = 0 To x - 1
            ret2(i, 0) = ret(i, 0)
        Next
        GetCutList = ret2
    Else
        GetCutList = Array(Empty, Empty)
    End If
End Function
 
Upvote 0
Many thanks,
Ive now used the above code & all good.

My next step now if anybody can advise is when i make a selection from the ListBox it then opens up UserForm HONDAPARTS with the selected item loaded.

From a worksheet i was using it would work like this.
Select from drop down list "CELL A17"
Press command button "HONDA EPC NUMBER" of which run the macro below to do what i mentioned above.


Rich (BB code):
Sub HondaEpcNumber_Click()


If Cells(17, "A") <> "" Then
        HondaParts.MyPartNumber.Value = Cells(17, "A")
        HondaParts.Show
    Else
        MsgBox "You didn`t enter a Honda Part Number In Cell A17", vbExclamation, "Honda Number - My Number Look Up"
    End If
End Sub

So make selection from ListBox1, Open HONDAPARTS & have selected part already loaded,see my example.
 

Attachments

  • 588.jpg
    588.jpg
    36.3 KB · Views: 4
  • 590.jpg
    590.jpg
    142 KB · Views: 4
Upvote 0
Can you advise correctly on my attempt please.

Rich (BB code):
Private Sub ListBox1_Click()
       Unload Me
       HondaParts.MyPartNumber.Value = ListBox1.Value
       HondaParts.Show
End Sub

All works apart from loading ListBox1 selection when HondaParts userform opens.

Thansk
 
Upvote 0
Rich (BB code):
Private Sub ListBox1_Click()
       Unload Me
       HondaParts.MyPartNumber.Value = ListBox1.List.Value
       HondaParts.Show
End Sub
 
Upvote 0
Below code returns the position of the value in the list BUT i would like the value & not its position

Rich (BB code):
Private Sub ListBox1_Click()
       Unload Me
       HondaParts.MyPartNumber.Value = ListBox1.ListIndex
       
       HondaParts.Show
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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