PDA

View Full Version : Solved: Adding another column to a listbox?



KennyJ
09-24-2008, 05:47 AM
What do I need to add this this Sub to add the information from column "A" to the left of the Information being populated from Column "D" in my List box?

'Populate a multi-column combo and list box with values from spreadsheet
'when the form is loaded
Private Sub UserForm_activate()

Dim MyList(400, 3) 'as array type
Dim R As Integer
Set WS = Workbooks("COMPONENTS_33.xls").Sheets("Sheet1")

'You can configure the number of columns, their width and height below
'as well as change the text in the ControlTipText of the listbox
Application.ShowToolTips = True
With ListBox1
.ColumnCount = 1
.ColumnWidths = 75
.Width = 100
.Height = 110
.ControlTipText = ""
End With


With WS

'If you have more than 500 rows change this number and 1st line of this sub dim mylist
For R = 1 To 400
MyList(R, 0) = .Range("D" & R + 1)

Next R
End With

'populate the list box
ListBox1.List = MyList
TextBox1.SetFocus
End Sub

Kenneth Hobs
09-24-2008, 09:19 AM
Not sure why you are setting listbox properties at runtime. You set the column count to 0. Why not 2? I set mine to 2 during design time.

Here is one approach.
Private Sub UserForm_Initialize()
Dim aCol As Range, eCol As Range, a() As Variant
Dim lc As Long, i As Long
Set aCol = Range("A1", Cells(Rows.Count, "A").End(xlUp))
Set eCol = Range("E1", Cells(Rows.Count, "E").End(xlUp))
lc = aCol.Count
If eCol.Count > lc Then lc = eCol.Count
ReDim a(1 To lc, 1 To 2)
On Error Resume Next
For i = 1 To lc
a(i, 1) = aCol.Item(i, 1)
a(i, 2) = eCol.Item(i, 1)
Next i
ListBox1.List = a()
End Sub

KennyJ
09-25-2008, 07:20 AM
What is the proper syntax for selecting the information in the second column of the list box when a row has already been selected?

Kenneth Hobs
09-25-2008, 07:29 AM
The listbox property BoundColumn is set to 1 by default. Change it to 2. Then use the listbox Value property as usual.

KennyJ
09-25-2008, 07:33 AM
Figured it out rookie mistake I was using 2 as my column and I needed 1 because they start at 0 duh