display more info for listbox entry

[Guest]

Hi,
I have a database on a worksheet and a userform to search one column for a
text string that the user puts in a textbox. The listbox shows just the
filtered cells.
I'd like the user to be able to select an item in the listbox and view
further info for that item (ie the values of other cells in the same row).
I'd put them as the caption of a label or some other "untouchable" way.
For example the text search looks in the product decription (column B) and
I'd like the user to see the product code (column A) and warehouse
availability (Column E).

 

[paul.robinson]

Hi
You need to put some code in the Listbox click event. I'll assume your
RowSource for the listbox is in A1:B3, so you have three rows of 3
columns. In the width property for the listbox I'll assume columns 2
and 3 have 0 width so you can't see them (I THINK you do this with :0:0
in the width property - check the help). Then

Private Sub Listbox1_Click()
Dim i as integer
With Listbox1
for i = 0 to .ListCount - 1
If .Selected(i) then
Userform1.LB1.Text = .List(i,1)
Userform1.LB2.Text = .List(i,2)
End If
Exit for
next i
End With
End Sub

If you double click your ListBox in the VBE you will see this event in
the drop down menu at the top right (if it is not created for you on
the click)
regards
Paul

 

[paul.robinson]

Hi
To populate the listbox you can create an array and add it to the
listbox using the .List method. your non-continuous range has to be
worked around.
e.g.
Suppose you have three columns of data. Suppose also that these are in
columns I, J and K and that the column you want to see in the listbox
is column I. For the listbox set Columncount to 3 and set ColumnWidths
to ;0;0. This means that the first column will take up the width of the
listbox. Then

Private Sub UserForm_Initialize()
Dim rng As Range, cell As Range
Dim ListValues(0 To 2) As Variant, ListArray() As Variant
Dim ListCollection As New Collection
Dim ListCount As Long
With Worksheets("Test").Range("I1") 'don't need all of column I
Set rng = .Range(.Cells(1, 1), .End(xlDown))
End With
For Each cell In rng
If InStr(1, cell, Parola.Value, vbTextCompare) Then
ListValues(0) = cell.Value
ListValues(1) = cell.Offset(0, 1).Value
ListValues(2) = cell.Offset(0, 2).Value
ListCollection.Add ListValues
End If
Next
ListCount = ListCollection.Count
ReDim ListArray(0 To ListCount - 1, 0 To 2)
For i = 0 To ListCount - 1
For j = 0 To 2
ListArray(i, j) = ListCollection(i + 1)(j)
Next j
Next i
ListBox1.List = ListArray
End Sub

Arrays in Listbox's must start at 0 when you count them. Items in
collections must start at 1 when you count them. Hence I'm starting the
loops at 0 but need an i+1 in the collection.

ListCollection(i + 1)(j)

refers to column j (which is 0 to 2) in ListCollection item (i+1) which
can go from item 1 to item listCount.

hope that helps!
regards
Paul

 

[paul.robinson]

Hi
Sorry, I don't know what you are asking for in the last paragraph. Can
you spell it out a bit.

The columnwidths worked for me. I set it in the properties window of
the listbox (the bottom left hand pane when you click on the listbox in
the editor)
regards
Paul

Thanks for your help (so far) Paul, but don't think I'm going to let you get
off so easily...
I'm trying to get my head round this. I lost a lot of time trying to get
ColumnWidths to work until I tried putting the values in quotes which solved
the problem (although the help file shows it without!)
You've incorporated the sub which I had on a button to be pressed after
inputting the text to look for in a textbox ("Parole"), so how can I initiate
the search ?

 

[paul.robinson]

Hi
The way I suggested in an earlier post.

Is the button you are clicking on the form with the listbox? If it is,
then it is very strange to populate the listbox using a button on the
same form - why not do the populating in the initialize sub as I
suggested? Now use your button to run the lable changing code.

regards
Paul

 

[Guest]

The TextBox "Parole" is still empty when you open the form so the initialize
function will do a search for nothing and populate the listbox with the whole
column and nothing changes when I type the text to find.
My way the form opens with an empty textbox and an empty list. I type the
word to search for and click (which is how websearches do it so is the way
people expect it to work). That populates the textbox with the full text only
from those cells containing the word I want.
Clicking on each item should display further details (as label captions) and
as I see that's what you addressed with the very first post. Duh!
Duh again! for missing the ColumnWidth in the properties window.
I'll work on what you've given me and will post back.

 

[paul.robinson]

Hi
That makes more sense then!
In the click event for the textbox you will need to run the code I put
in the initialisation sub. The thing you are searching for will be
Userform1.TB1.Text
in your If Then Else

regards
Paul

 

[paul.robinson]

Hi
replied privately, but I'll put the fix here to finish off the thread

Private Sub Listbox1_Click()
With ListBox1
UserForm1.Label1.Caption = .List(.ListIndex, 1)
UserForm1.Label2.Caption = .List(.ListIndex, 2)
End With
Me.Repaint
End Sub

regards
Paul

Thanks again,
The search part is working perfectly but the ListBox_Click only works with
the first item.