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

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

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
>
>
> "(E-Mail Removed)" 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
> >
> >

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

#DIV/0 wrote:

> 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 ?
>
>
>
> --
> David M
>
>
> "(E-Mail Removed)" wrote:
>
> > 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
> > >
> > >
> > > "(E-Mail Removed)" 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
> > > >
> > > >
> >
> >

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

#DIV/0 wrote:

> Hang on. Figured it. I left
> ListBox1.ColumnCount = 3
> ListBox1.ColumnWidths = ";0;0"
> in UserForm_Initialize and put all the rest in the button_Click
>
> Now I want the ListBox1_Click to put the values of the hidden columns into
> label captions. How do I do that ? ListIndex ? Offset ?
>
> --
> David M

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.

--
David M


"(E-Mail Removed)" wrote:

> 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
>
> #DIV/0 wrote:
>
> > Hang on. Figured it. I left
> > ListBox1.ColumnCount = 3
> > ListBox1.ColumnWidths = ";0;0"
> > in UserForm_Initialize and put all the rest in the button_Click
> >
> > Now I want the ListBox1_Click to put the values of the hidden columns into
> > label captions. How do I do that ? ListIndex ? Offset ?
> >
> > --
> > David M
>
>

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


#DIV/0 wrote:

> 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.
>
> --
> David M
>
>
> "(E-Mail Removed)" wrote:
>
> > 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
> >
> > #DIV/0 wrote:
> >
> > > Hang on. Figured it. I left
> > > ListBox1.ColumnCount = 3
> > > ListBox1.ColumnWidths = ";0;0"
> > > in UserForm_Initialize and put all the rest in the button_Click
> > >
> > > Now I want the ListBox1_Click to put the values of the hidden columns into
> > > label captions. How do I do that ? ListIndex ? Offset ?
> > >
> > > --
> > > David M
> >
> >

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

#DIV/0 wrote:

> Thanks again,
> The search part is working perfectly but the ListBox_Click only works with
> the first item.
>
> --
> David M
>
>
> "(E-Mail Removed)" wrote:
>
> > 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
> >
> >
> > #DIV/0 wrote:
> >
> > > 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.
> > >
> > > --
> > > David M
> > >
> > >
> > > "(E-Mail Removed)" wrote:
> > >
> > > > 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
> > > >
> > > > #DIV/0 wrote:
> > > >
> > > > > Hang on. Figured it. I left
> > > > > ListBox1.ColumnCount = 3
> > > > > ListBox1.ColumnWidths = ";0;0"
> > > > > in UserForm_Initialize and put all the rest in the button_Click
> > > > >
> > > > > Now I want the ListBox1_Click to put the values of the hidden columns into
> > > > > label captions. How do I do that ? ListIndex ? Offset ?
> > > > >
> > > > > --
> > > > > David M
> > > >
> > > >
> >
> >