List Box Match

H

Hazel

Hi All

I have adapted the code below from something I found in this forum

Private Sub Cmd1_Click()
Dim rngToSearch As Range
Dim rngFound As Range
Set rngToSearch = ActiveSheet.Columns("F")
Set rngFound = rngToSearch.Find(What:=Tb1.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry " & Tb1.Value & " was not found."
Else
rngFound.Select

End If

End Sub

It works OK and finds the number I'm searching for in Column "F" what I
would like to happen is that on finding the correct selection it will also
highlight the row in the list box "Lb1" which also contains the same number
in Column 5 in the list box ,it would then display the information in a new
TextBox "Tb2" -- I would then not have to keep closing the UserForm to read
all the info. Hope I have expained it all properly.
 
T

Tom Ogilvy

Maybe something like this:

Private Sub Cmd1_Click()
Dim rngToSearch As Range
Dim rngFound As Range
Set rngToSearch = ActiveSheet.Columns("F")
Set rngFound = rngToSearch.Find(What:=Tb1.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry " & Tb1.Value & " was not found."
Else
With Lb1
for i = 0 to .listcount - 1
if .list(i,4) = tb1.value then
lb1.ListIndex = i
for each cell in Range(cells(rngFound.row,1), _
cells(rngFound,row,6)) '<== change the 6 to reflect number of
columns
s = cell.Value & ","
Next
TB2.value = Left(s,len(s)-1)
exit for
end if
Next
rngFound.Select
End With
End If


End Sub
 
H

Hazel

Hi Tom

Thank you for the response however the following error is cropping up

Compile Error.

Wrong number of arguments or invalid property assignment

Have commented out where the error crops up does it matter that I'm using
RowSource to fill "Lb1"
 
T

Tom Ogilvy

cells(rngFound,row,6)) has a typo

cells(rngFound.row,6))

The code was a suggested approach. It wasn't intended to be a paste in
solution. It hasn't been tested.
 
H

Hazel

Hi Tom

I did paste it and I tend not to alter anything until I can get it working
and comma's and full stops are all part of Excel and I would never know if
one was right or wrong. Anyway got it working and doing just what I want -
you guys amaze me sometimes with your knowledge how do you remember it all ???
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top