list all the find values
 

I am trying to do the following.

I have a list of names in sheet1 column b - b2:B500

I have a user form that I would like to do the following

the user can enter any part of a name into a text box and the list bo
with the names the user can choose will only have those that contai
what he just entered.

for example

a user is trying to find a customers name. The current list box the
can choose from has all names for 500 customers. To make it simpl
there will be a text box that allows them to type in any part of th
customers name and then it will repopulate the list box with th
resulting values of the search term. match case is false....

Thanks for any help...

Jos

--
jhahe
-----------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...fo&userid=2359
View this thread: http://www.excelforum.com/showthread.php?threadid=55159

list all the find values
 

Private Sub Textbox1_Change()
Dim rng as Range, v as Variant
Dim s1 as String, s as String
Dim i as Long, l as Long
with worksheets("Data")
set rng = .Range("B2:B500")
End with
v = rng
Listbox1.clear
s1 = lcase(Textbox1.Value)
l = len(s1)
for i = 1 to 499
s = v(i,1)
if lcase(left(s,l)) = s1 then
AddItem s
end if
next i
end Sub

--
Regards,
Tom Ogilvy

"jhahes" wrote:


I am trying to do the following.

I have a list of names in sheet1 column b - b2:B500

I have a user form that I would like to do the following

the user can enter any part of a name into a text box and the list box
with the names the user can choose will only have those that contain
what he just entered.

for example

a user is trying to find a customers name. The current list box they
can choose from has all names for 500 customers. To make it simple
there will be a text box that allows them to type in any part of the
customers name and then it will repopulate the list box with the
resulting values of the search term. match case is false....

Thanks for any help...

Josh


--
jhahes
------------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=551594

list all the find values
 

i tried the code and it gives me this error

sub or function not defined AND

AddItem is highlighted when this happens....

thanks

Josh


--
jhahes
------------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=551594

list all the find values
 

Just a typo
Additem s

should be

ListBox1.AddItem s

I tested it with that correction and it worked fine for me. (lightly
tested).

--
Regards,
Tom Ogilvy


"jhahes" wrote:


i tried the code and it gives me this error

sub or function not defined AND

AddItem is highlighted when this happens....

thanks

Josh


--
jhahes
------------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=551594

list all the find values
 

I like what the code does but you have to type in the whole name for it
to find matching entries.

Is there anyway that it can do this

if you type in say (joh) in the text field and it finds all the
following because they contain joh in part of the word

john smith
tom johnson
john davis
johan smith

Thanks for any help...

Josh


--
jhahes
------------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=551594

list all the find values
 

Private Sub Textbox1_Change()
Dim rng as Range, v as Variant
Dim s1 as String, s as String
Dim i as Long, l as Long
with worksheets("Data")
set rng = .Range("B2:B500")
End with
v = rng
Listbox1.clear
s1 = lcase(Textbox1.Value)
l = len(s1)
for i = 1 to 499
s = v(i,1)
if Instr(1,s,s1,vbTextCompare) 0 then
AddItem s
end if
next i
end Sub

--
Regards,
Tom Ogilvy


"jhahes" wrote in
message ...

I like what the code does but you have to type in the whole name for it
to find matching entries.

Is there anyway that it can do this

if you type in say (joh) in the text field and it finds all the
following because they contain joh in part of the word

john smith
tom johnson
john davis
johan smith

Thanks for any help...

Josh


--
jhahes
------------------------------------------------------------------------
jhahes's Profile:
http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=551594