paul.robinson
12/13/2006 11:27:00 AM
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
>
>
> "paul.robinson@it-tallaght.ie" 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
> > >
> > >
> > > "paul.robinson@it-tallaght.ie" 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
> > > >
> > > >
> >
> >