Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-02-2018, 08:23 AM
14spar15 14spar15 is offline Add additional column to a listbox in form. Windows XP Add additional column to a listbox in form. Office 2000
Advanced Beginner
Add additional column to a listbox in form.
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 06-02-2018, 03:03 PM
NoSparks NoSparks is offline Add additional column to a listbox in form. Windows 7 64bit Add additional column to a listbox in form. Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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
but you don't have to do it like that, this should do
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
Have a look at this page, I'm sure it will be of interest.
Reply With Quote
  #3  
Old 06-02-2018, 06:01 PM
14spar15 14spar15 is offline Add additional column to a listbox in form. Windows XP Add additional column to a listbox in form. Office 2000
Advanced Beginner
Add additional column to a listbox in form.
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

Great!!! Thanks so much..
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Add additional column to a listbox in form. 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
Add additional column to a listbox in form. 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

Other Forums: Access Forums

All times are GMT -7. The time now is 03:01 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft