PDA

View Full Version : List box from columns?



lifeson
09-05-2007, 06:26 AM
I have a user form which I need to place a listbox
The list box has 2 columns the first colum will always come from column A but I want the second column in the listbox to be from any other column on the same worksheet.
The worksheet column for the second column in the list box would be determined by code

I suppose something like vlookup but not sure if this is possible :think: :doh:

Norie
09-05-2007, 06:30 AM
How would the 2nd column be determined?

lifeson
09-05-2007, 06:37 AM
Not sure yet
I have a seperate sheet with lots of componets listed.
A column on that sheet will have record to show which column from the sheet which is used as the listbox source is to be used (HLOOKUP ?)

Norie
09-05-2007, 06:49 AM
If you aren't sure how to determine the column how will VBA, the listbox or us be able to determine it?:)

PS I'm not sure how VLOOKUP or HLOOKUP would come into it.

rory
09-05-2007, 07:08 AM
Once you have your column number, you can use something like:

With ListBox1
.AddItem cells(row, "A").Value
.List(.ListIndex, 1) = Cells(row, columnnumber).Value

lifeson
09-05-2007, 08:04 AM
Rory I think I undertsand the principl but just cant get the second column to populate

rory
09-05-2007, 08:20 AM
My fault as I copied the wrong chunk of code for you (Having one of those days today). Your code for userform2 would be something like:
Private Sub UserForm_Initialize()
Dim i As String
Dim c As String
Dim N As Long
c = UserForm1.ListBox1.Value
i = WorksheetFunction.VLookup(c, Range("database"), 2, False)
MsgBox c & "selected, look up list from column " & i
Sheets("Sheet2").Select
With ListBox1
'set first column
For N = 2 To 15
.AddItem Cells(N, "A").Value
.List(.ListCount - 1, 1) = Cells(N, i).Value
Next N
End With
End Sub

lifeson
09-05-2007, 10:29 AM
Thanks Rory thats worked fine
I'll ask this one but I dont think it can be done
Can the text in each column be aligned differently
i.e column 1 textalignright & column 2 textalignleft

Oh, and how would I add column headers to the list as it is not pulling the data from a named range?

Norie
09-05-2007, 11:09 AM
I'm sorry but the answers to those 2 questions are basically no.

You could perhaps create some sort of workaround for 1 eg padding with spaces

But the only way to have 'real' headers is if you populate from a range.

PS That doesn't actually need to be a named range.:)

Charlize
09-05-2007, 02:20 PM
Thanks Rory thats worked fine
I'll ask this one but I dont think it can be done
Can the text in each column be aligned differently
i.e column 1 textalignright & column 2 textalignleft

Oh, and how would I add column headers to the list as it is not pulling the data from a named range?Take a look at the listview control version 6 sp 2. You have to add this control to the form controls. Once added to the toolbox you can add a listview to your form. This one can use headers.