Type in TextBox to show values in ListBox advice

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
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: 5
  • 586.jpg
    586.jpg
    35.8 KB · Views: 5
L

Legacy 456155

Guest
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
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
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: 2
  • 590.jpg
    590.jpg
    142 KB · Views: 2

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
Rich (BB code):
Private Sub ListBox1_Click()
       Unload Me
       HondaParts.MyPartNumber.Value = ListBox1.List.Value
       HondaParts.Show
End Sub
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
Now done by moving the unload me line
 

Watch MrExcel Video

Forum statistics

Threads
1,119,102
Messages
5,576,135
Members
412,699
Latest member
Dmetcalf2021
Top