-
Solved: Adding another column to a listbox?
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?
[vba]'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
[/vba]
-
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.
[vba]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[/vba]
-
What is the proper syntax for selecting the information in the second column of the list box when a row has already been selected?
-
The listbox property BoundColumn is set to 1 by default. Change it to 2. Then use the listbox Value property as usual.
-
Figured it out rookie mistake I was using 2 as my column and I needed 1 because they start at 0 duh
Last edited by KennyJ; 09-25-2008 at 07:52 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules