#1
|
|||
|
|||
Add additional column to a listbox in form.
I’m trying to add a 3rd column to a listbox that is in a form. This 3rd column will be data from column #4 on my sheet. Here is the original code that works fine with 2 columns in the listbox (labeled “Original”). I tried to modify the coding (See “New”) but the 3rd column is not being brought in to the listbox. If I experiment with the numbers here
strList(i, 1) = Cells(i + 2, 2).Value strList(i, 0) = Cells(i + 2, 3).Value strList(i, 2) = Cells(i + 2, 4).Value 'This line has been added to original code I can see the data in the listbox from column 4 (on sheet) but I then loose either column 2 or 3 (From Sheet). Basically it seems a 3rd column is not being brought into the listbox. I hope this all makes sense. ***Original *** Private Sub UserForm_Initialize() 'this will populate listbox1 with descriptions Dim strList() As String Dim intTitleColIndex As Integer Dim intLastRow As Integer Dim i As Integer, j As Integer 'find where Title column is on sheet (note: should be column 2) For i = 1 To 5 If Cells(1, i).Value = "Item Title" Then intTitleColIndex = i Exit For End If Next 'reset variable length array to total number of descriptions in col 2 intLastRow = WorksheetFunction.CountA(Sheet1.Columns(intTitleCo lIndex)) ReDim strList(intLastRow, intLastRow) 'load array with all descriptions(this may be unneccessary but am doing it incase i need to manipulate array info later) For i = 0 To intLastRow 'using i to indicate array position as well as row in description column (should always be off by 2) strList(i, 1) = Cells(i + 2, 2).Value strList(i, 0) = Cells(i + 2, 3).Value Next 'load listbox with array strings from title column With ListBox1 .ColumnHeads = False .ColumnCount = 2 .ColumnWidths = "50;50" For i = 0 To intLastRow .AddItem strList(i, 0) .List(ListBox1.ListCount - 1, 1) = strList(i, 1) Next End With End Sub ***New *** Private Sub UserForm_Initialize() 'this will populate listbox1 with descriptions Dim strList() As String Dim intTitleColIndex As Integer Dim intLastRow As Integer Dim i As Integer, j As Integer 'find where Title column is on sheet (note: should be column 2) For i = 1 To 5 If Cells(1, i).Value = "Item Title" Then intTitleColIndex = i Exit For End If Next 'reset variable length array to total number of descriptions in col 2 intLastRow = WorksheetFunction.CountA(Sheet1.Columns(intTitleCo lIndex)) ReDim strList(intLastRow, intLastRow) 'load array with all descriptions(this may be unneccessary but am doing it incase i need to manipulate array info later) For i = 0 To intLastRow 'using i to indicate array position as well as row in description column (should always be off by 2) strList(i, 1) = Cells(i + 2, 2).Value strList(i, 0) = Cells(i + 2, 3).Value strList(i, 2) = Cells(i + 2, 4).Value 'This line has been added to original Next 'load listbox with array strings from title column With ListBox1 .ColumnHeads = False .ColumnCount = 3 'This was 2 in original .ColumnWidths = "150;150;150" 'This was .ColumnWidths = "50;50" in original For i = 0 To intLastRow .AddItem strList(i, 0) .List(ListBox1.ListCount - 1, 1) = strList(i, 1) Next End With End Sub |
#2
|
|||
|
|||
While you've added the third column to the array, you've omitted adding it to the listbox
Code:
'load listbox with array strings from title column With ListBox1 .ColumnHeads = False .ColumnCount = 3 'This was 2 in original .ColumnWidths = "150;150;150" 'This was .ColumnWidths = "50;50" in original For i = 0 To intLastRow .AddItem strList(i, 0) .List(ListBox1.ListCount - 1, 1) = strList(i, 1) .List(ListBox1.ListCount - 1, 2) = strList(i, 2) '<~~ third column Next End With Code:
'load listbox with array strings from title column With ListBox1 .ColumnHeads = False .ColumnCount = 3 'This was 2 in original .ColumnWidths = "150;150;150" 'This was .ColumnWidths = "50;50" in original .List = strList End With |
#3
|
|||
|
|||
Great!!! Thanks so much..
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Display an additional date column in Gantt chart | ds1 | Project | 4 | 04-17-2017 01:34 PM |
This is a Userform LIstbox queston: A variable does not set to the value of a listbox | CatMan | Excel Programming | 14 | 08-18-2014 08:14 PM |
This is a Userform LIstbox queston: A variable does not set to the value of a listbox | CatMan | Excel | 1 | 08-08-2014 09:41 AM |
Using form to populate additional fields | hmccain | Word | 0 | 12-09-2013 11:15 AM |
as the return value of another column, using Listbox? | marreco | Excel Programming | 1 | 03-27-2012 12:48 PM |