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

 

[vmjan02]

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

Thanks I missed the property.

Just modified the code

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

With ListBox1
.ColumnCount = 3
.ColumnWidths = "70,40,20"
.Value = f
End With

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

Thanks a ton. it is working all fine now. Thanks.

 

[DanteAmor]

Thanks I missed the property.

Just modified the code

Thanks a ton. it is working all fine now. Thanks.

ok, but, it is advisable to put the properties at the beginning of the execution of the userform, it can be in the Activate event or in the Initialize event:

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


[COLOR=#0000ff]Private Sub UserForm_Activate()[/COLOR]
[COLOR=#0000ff]  With ListBox1[/COLOR]
[COLOR=#0000ff]    .ColumnCount = 3[/COLOR]
[COLOR=#0000ff]    .ColumnWidths = "70,40,20"[/COLOR]
[COLOR=#0000ff]    .Value = f[/COLOR]
[COLOR=#0000ff]  End With[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

Of course the most important thing is that it works for you.

 

[vmjan02]

yes true. But I did not wanted it at the beginning, so did it at the time of search.

 

[DanteAmor]

Ok