Text Box and List Box

[vmjan02]

I have the user form with text box and list box

With the below code I am trying to search the customer number while the user enter the name in text box 11 and the list box will display the result.
But this code is not working.

In column B the name is entered and it starts with row 7. The list box should display search details from B to D


Help! as not sure Why the code is not working.
Code.

Private Sub TextBox11_Change()
Dim i As Long
On Error Resume Next
Me.TextBox11.Text = StrConv(Me.TextBox11.Text, vbProperCase)
Me.ListBox1.Clear
For i = 7 To Application.WorksheetFunction.CountA(Sheet1.Range("B:B"))
For x = 1 To 3
a = Len(Me.TextBox11.Text)
If Left(Sheet1.Cells(i, x).Value, a) = Me.TextBox11.Text And Me.TextBox11.Text <> "" Then
Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value
For c = 1 To 3
Me.ListBox1.List(ListBox1.ListCount - 1, c) = Sheet1.Cells(i, c + 1).Value
Next c
End If
Next x
Next i
End Sub

 

[theBardd]

This works for me

Private Sub TextBox11_AfterUpdate()
Dim i As Long, c As Long
 
    On Error Resume Next
    With Me
 
        With .TextBox11
        
            .Text = StrConv(.Text, vbProperCase)
        End With
     
        .ListBox1.Clear
        For i = 7 To Application.CountA(Sheet1.Range("B:B")) + 6
     
            If Sheet1.Cells(i, "B").Value = .TextBox11.Text And .TextBox11.Text <> vbNullString Then
     
                .ListBox1.AddItem Sheet1.Cells(i, "A").Value
        
                For c = 1 To 3
        
                    .ListBox1.List(ListBox1.ListCount - 1, c) = Sheet1.Cells(i, c + 1).Value
                Next c
            End If
        Next i
    End With
End Sub

 

[vmjan02]

For me it is still not working. List box is not displaying anything

Private Sub TextBox11_Change()


Dim i As Long, c As Long

On Error Resume Next
With Me

With Me.TextBox11

.Text = StrConv(.Text, vbProperCase)
End With

.ListBox1.Clear
For i = 7 To Application.CountA(Sheet1.Range("B:B")) + 6

If Sheet1.Cells(i, "B").Value = .TextBox11.Text And .TextBox11.Text <> vbNullString Then

.ListBox1.AddItem Sheet1.Cells(i, "A").Value

For c = 1 To 3

.ListBox1.List(ListBox1.ListCount - 1, c) = Sheet1.Cells(i, c + 1).Value
Next c
End If
Next i
End With
End sub

 

[theBardd]

Post the data table then, otherwise we are working blind.

 

[NoSparks]

In column B the name is entered and it starts with row 7. The list box should display search details from B to D

Are the names sorted alphabetically ?
If yes, try this

Private Sub TextBox11_Change()
'filter as you type, sheet1 column B sorted names
    Dim lr As Long, theName As String
    Dim filtRng As Range, arr As Variant

With Sheet1
    lr = .Range("B" & .Rows.Count).End(xlUp).Row
    Set filtRng = .Range("B6:D" & lr)   'assume row 6 is headers
End With

If Len(Me.TextBox11.Value) = 0 Then
    Me.ListBox1.List = filtRng.Offset(1).Value
    Exit Sub
End If

theName = Me.TextBox11.Value & "*"

Application.ScreenUpdating = False

filtRng.AutoFilter Field:=1, Criteria1:=theName
arr = filtRng.Offset(1).SpecialCells(xlCellTypeVisible).Value
Me.ListBox1.List = arr
filtRng.AutoFilter

Application.ScreenUpdating = True

End Sub

 

[DanteAmor]

I think one of the problems is that you are converting the text to ProperCase, then convert the cell data to ProperCase to make the comparison. To avoid this, I use the Find () method.


Another detail, when you convert the textbox to vbProperCase, you are modifying the textbox, that way the Change event is executed again. To avoid this I use a global variable.

Try this

Dim loading As Boolean  'global declaration, at the beginning of all the code


Private Sub TextBox11_Change()
  Dim r As Range, f As Range, cell As String, i As Long
  If loading = True Then Exit Sub
  loading = True
  TextBox11.Text = StrConv(Me.TextBox11.Text, vbProperCase)
  loading = False
  ListBox1.Clear
  If TextBox11.Value = "" Then Exit Sub
  Set r = Sheet1.Range("B7", Range("B" & Rows.Count).End(xlUp))
  Set f = r.Find(TextBox11 & "*", LookIn:=xlValues, lookat:=xlWhole)
  If Not f Is Nothing Then
      cell = f.Address
      Do
          ListBox1.AddItem f
          For i = 1 To 3
            Me.ListBox1.List(ListBox1.ListCount - 1, i) = f.Offset(, i)
          Next
          Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
  End If
End Sub

 

[vmjan02]

Hi DanteAmor
Cool, working all find, but the only concern is the list box is not display the details of column c,d it is just displaying the details of column B only. list box should display the coulmns B,C and D with respect to search.

 

[vmjan02]

Are the names sorted alphabetically ?
If yes, try this

Private Sub TextBox11_Change()
'filter as you type, sheet1 column B sorted names
    Dim lr As Long, theName As String
    Dim filtRng As Range, arr As Variant

With Sheet1
    lr = .Range("B" & .Rows.Count).End(xlUp).Row
    Set filtRng = .Range("B6:D" & lr)   'assume row 6 is headers
End With

If Len(Me.TextBox11.Value) = 0 Then
    Me.ListBox1.List = filtRng.Offset(1).Value
    Exit Sub
End If

theName = Me.TextBox11.Value & "*"

Application.ScreenUpdating = False

filtRng.AutoFilter Field:=1, Criteria1:=theName
arr = filtRng.Offset(1).SpecialCells(xlCellTypeVisible).Value
Me.ListBox1.List = arr
filtRng.AutoFilter

Application.ScreenUpdating = True

End Sub

Cool, working all find, but the only concern is the list box is not display the details of column c,d it is just displaying the details of column B only. list box should display the columns B,C and D with respect to search

 

[DanteAmor]

Hi DanteAmor
Cool, working all find, but the only concern is the list box is not display the details of column c,d it is just displaying the details of column B only. list box should display the coulmns B,C and D with respect to search.

That's because you have the property of ColumnCount in 1.
Change the ColumnCount property of the Listbox to 3

 

[NoSparks]

Cool, working all find, but the only concern is the list box is not display the details of column c,d it is just displaying the details of column B only. list box should display the columns B,C and D with respect to search

Like DanteAmor says.
Make sure you also adjust the ColumnWidths as necessary.