Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
listbox help please
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 -- Gary |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
listbox help please
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 -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "Gary Keramidas" wrote: 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 -- Gary |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
listbox help please
thanks martin
-- Gary "Martin Fishlock" wrote in message ... 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 -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "Gary Keramidas" wrote: 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 -- Gary |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
listbox help please
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. -- Gary "Martin Fishlock" wrote in message ... 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 -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "Gary Keramidas" wrote: 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 -- Gary |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
listbox help please
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. -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "Gary Keramidas" wrote: 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. -- Gary "Martin Fishlock" wrote in message ... 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 -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "Gary Keramidas" wrote: 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 -- Gary |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
listbox help please
ok, thanks. i'll just stick with the original individual textboxes.
-- Gary "Martin Fishlock" wrote in message ... 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. -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "Gary Keramidas" wrote: 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. -- Gary "Martin Fishlock" wrote in message ... 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 -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "Gary Keramidas" wrote: 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 -- Gary |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
userform listbox cannot get listbox.value to transfer back to main sub | Excel Programming | |||
avoiding duplicates in listbox (added from another listbox) | Excel Programming | |||
VBA: Creating listbox similar to the one in Pivot table (Listbox+Checkbox) | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming |