UserFrom TextBox search in ListBox in the same UserForm?

[k0st4din]

Hello friends.
I am again with a question for you gods of macros.
Here it is:
I have in the excel file:
userform in it I have:
ListBox1 and also TextBox1 (and other things), but I am interested how it began to look certain things (in this case name) starts to show me (filtered) only sought or possible. ie something like Andy or Andy Garcia or Gusto Andy Perfetsto, show me all the options (I think it's sort of IF contains).
Attach some pictures and excel file to target, of course, if this could happen.

[streub]

I am not sure of exactly what you want or need but if I were to guess, your initial goal is to enter a name in the search window and have only that name or similar names appear in the list box. From that list you select the name(s) that meet your criterion. Is this what you want to accomplish?

[k0st4din]

Hello
when you look at the file will surely see that I have other buttons at the bottom, which are triggered by other macros.
I mean when I type the name I want (maybe not first) - >>> just show it to me (filtered) name(s) and then I'll put myself beak (tick) that precede the name in (UserForm).
Here on this site (http://dailydoseofexcel.com/archives/2005/02/16/limit-a-listbox/) I found what I want, but it will have to be changed to 100%.

I am not sure of exactly what you want or need but if I were to guess, your initial goal is to enter a name in the search window and have only that name or similar names appear in the list box. From that list you select the name(s) that meet your criterion. Is this what you want to accomplish? ->>> YES

[k0st4din]

Okay, friends, this macro works, but can you help me and tell me where to put the asterisk (*) to be able to search any word, not just the first word.
Now do the following:
Ivan Petrovski Kadvistinski
If I write Ivan - I found the name, but if you write Petrovski - not him alone.
After a night of surfing the web mention of this (*) to search for any word.
Thank you in advance, and you may think of another solution.



Private Sub TextBox1_Change()Dim i As Long
Dim sFind As String

sFind = Me.TextBox1.Text

If Len(sFind) = 0 Then
Me.ListBox1.ListIndex = -1
Me.ListBox1.TopIndex = 0
Else
For i = 0 To Me.ListBox1.ListCount - 1
If UCase(Left(Me.ListBox1.List(i), Len(sFind))) = UCase(sFind) Then
Me.ListBox1.TopIndex = i
Me.ListBox1.ListIndex = i
Exit For
End If
Next i
End If
End Sub

[p45cal]

Try replacing:
If UCase(Left(Me.Listbox1.List(i), Len(sFind))) = UCase(sFind) Then
with:
If InStr(UCase(Listbox1.List(i)), UCase(sFind)) > 0 Then

[k0st4din]

Again and again you helped me.
Be alive and well.
A thousand thanks. :clap::bow:

[afgg]

Private Sub TextBox1_Change()Dim i As Long
Dim sFind As String

sFind = Me.TextBox1.Text

If Len(sFind) = 0 Then
Me.ListBox1.ListIndex = -1
Me.ListBox1.TopIndex = 0
Else
For i = 0 To Me.ListBox1.ListCount - 1
If UCase(Left(Me.ListBox1.List(i), Len(sFind))) = UCase(sFind) Then
Me.ListBox1.TopIndex = i
Me.ListBox1.ListIndex = i
Exit For
End If
Next i
End If
End Sub





I found this code helpful but i need a modification to this code if you could help me please. How can i get this code to display info of a whole row on a listbox instead of just a single column?


For example I want the list box display info of those columns

A - B - C - D
Name Marry 19 student.

[p45cal]

For 3 columns, change the named range to :
=OFFSET(Sheet1!$Z$1,0,0,COUNTA(Sheet1!$Z$2:$Z$104),3)
and change the ListBox's .ColumnCount property to 3

[afgg]

The code is on a userform.

do i add that on the listbox rowsource?

[p45cal]

The code is on a userform.

do i add that on the listbox rowsource?No, it should already be that, just as I said:
For 3 columns, change the named range to :
=OFFSET(Sheet1!$Z$1,0,0,COUNTA(Sheet1!$Z$2:$Z$104),3):
19032
and:
"change the ListBox's .ColumnCount property to 3":
19033

[Tenyasha]

¡¡Thank You so much!!. It helped me a lot

[jmtanoury]

Hello friends.
I am again with a question for you gods of macros.
Here it is:
I have in the excel file:
userform in it I have:
ListBox1 and also TextBox1 (and other things), but I am interested how it began to look certain things (in this case name) starts to show me (filtered) only sought or possible. ie something like Andy or Andy Garcia or Gusto Andy Perfetsto, show me all the options (I think it's sort of IF contains).
Attach some pictures and excel file to target, of course, if this could happen.

Need to post to access attachment

[SamT]

Kost4din's problem was solved 4 years ago.

This thread is now Closed for replies.