listbox help please

[Gary Keramidas]

was wondering if someone could give me the syntax to load this into a listbox
with 5 columns

these are the values i find and want to put them on 1 row of a listbox fill the
listbox with as many rows as the find returns:

Cells(rngfound.Row, "L").Value
Cells(rngfound.Row, "N").Value
Cells(rngfound.Row, "T").Value
Cells(rngfound.Row, "V").Value
Cells(rngfound.Row, "I").Value

 

[Guest]

Gary

Try this you may need to adjust it a little.

Note the cells uses numbers not letter for columns and I assume that you
have set five columns for the list box.

Private Sub UserForm_Initialize()
Dim rngfound As Range
Dim szFirstAddress As String

Set rngfound = .Find(......)
If Not rngfound Is Nothing Then
szFirstAddress = rngfound.Address
Do
With Me.ListBox1
.AddItem (Cells(rngfound.Row, 12).Value) 'L
.List(Me.ListBox1.ListCount - 1, 1) = _
.Cells(rngfound.Row, 14).Value 'N
.List(Me.ListBox1.ListCount - 1, 2) = _
.Cells(rngfound.Row, 20).Value 'T
.List(Me.ListBox1.ListCount - 1, 3) = _
.Cells(rngfound.Row, 22).Value 'V
.List(Me.ListBox1.ListCount - 1, 4) = _
.Cells(rngfound.Row, 9).Value 'I
End With
Set rngfound = .FindNext(rngfound)
Loop While Not rngfound Is Nothing And _
rngfound.Address <> szFirstAddress
End If

End Sub

 

[Gary Keramidas]

thanks martin

--


Gary

Gary

Try this you may need to adjust it a little.

Note the cells uses numbers not letter for columns and I assume that you
have set five columns for the list box.

Private Sub UserForm_Initialize()
Dim rngfound As Range
Dim szFirstAddress As String

Set rngfound = .Find(......)
If Not rngfound Is Nothing Then
szFirstAddress = rngfound.Address
Do
With Me.ListBox1
.AddItem (Cells(rngfound.Row, 12).Value) 'L
.List(Me.ListBox1.ListCount - 1, 1) = _
.Cells(rngfound.Row, 14).Value 'N
.List(Me.ListBox1.ListCount - 1, 2) = _
.Cells(rngfound.Row, 20).Value 'T
.List(Me.ListBox1.ListCount - 1, 3) = _
.Cells(rngfound.Row, 22).Value 'V
.List(Me.ListBox1.ListCount - 1, 4) = _
.Cells(rngfound.Row, 9).Value 'I
End With
Set rngfound = .FindNext(rngfound)
Loop While Not rngfound Is Nothing And _
rngfound.Address <> szFirstAddress
End If

End Sub

 

[Gary Keramidas]

one other question.

is there a way to align the data within each column? i'd like to right align
numbers, and left align text.

 

[Guest]

I don't thin you can set seperate left and right alignments for different
columns.

You can set left or right alignment for the listbox or you could pad the
numbers with leading spaces but you would need to ensure that you set the
font to a monospaced font like courier.

 

[Gary Keramidas]

ok, thanks. i'll just stick with the original individual textboxes.