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

#DIV/0 wrote:

Hi Paul,
So part of the trick is just having the data already there on the userform
but hidden. Clever....
Actually the list box is a single column and I'm geting the data from a
single column in the worksheet. I'll have to figure out how to set up the
listbox correctly as I wasn't doing it through initializing the form. I have
this sub populating it after a text search:

Dim rng As Range, cell As Range
With Worksheets("Offerte")
Set rng = .Range("I:I")
End With
ListBox1.Clear
For Each cell In rng
If InStr(1, cell, Parola.Value, vbTextCompare) Then
ListBox1.AddItem cell.Value
End If
Next

If I initialize the listbox with more columns (hiding all but one), how can
I get the results of this sub into the right column ?

--
David M


" wrote:

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

#DIV/0 wrote:

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).

--
David M