none
How to Create Listbox Control with More than 10 Columns RRS feed

  • Question

  • Trying to create a listbox control with 13 columns - one for each month and a totals column.  The additem method has a limitation of 10 columns.  I know there's a way around this and I've been able to find a couple of examples but they all involve creating a range in excel.  I'm using Word, so they don't really help.  I think there's a way to do it with an array also, but I haven't been able to get that to work.

    Any ideas?

    Thanks!

    Saturday, September 1, 2012 5:59 AM

Answers

  • Not sure that I am on the same wave length and so not sure if my answer is what you are looking for.

    I believe that AddItem is designed to adds rows of data to the list box; not columns.

    You can set the number of columns in the Properties when you create the list box in design mode. The column widths start off with a default width but you can also set the column widths in the properties. If setting column widths, start at about 20 wide and then adjust after testing because if you make them too small then you will not see any data in them because they are too narrow.

    The following code will create an array of 10 rows by 13 columns. It populates columns 1 to 12 of the array with random numbers and then sums those numbers and populates column 13 of the array. The array is then assigned to the listbox to populate.

    The example code was tested in the forms Initialize event so that the listbox is populated when the form opens. You can use simialr code anywhere.

    Private Sub UserForm_Initialize()

        Dim arrLstBox(1 To 10, 1 To 13)
        Dim i As Long
        Dim j As Long
        Dim lngSum As Long
      
        'Populate rows 1 to 10 and columns 1 to 12 with random numbers
        For i = 1 To 10
            For j = 1 To 12
                arrLstBox(i, j) = Int((10 * Rnd) + 1)
            Next j
        Next i
       
        'Populate last column with Sum of columns 1 to 12
        For i = 1 To 10
            lngSum = 0
            For j = 1 To 12
                lngSum = lngSum + arrLstBox(i, j)
            Next j
            arrLstBox(i, 13) = lngSum
        Next i
       
        Me.ListBox1.List = arrLstBox()
    End Sub


    Regards, OssieMac

    • Marked as answer by MavilaJay Saturday, September 1, 2012 5:43 PM
    Saturday, September 1, 2012 7:11 AM

All replies

  • Not sure that I am on the same wave length and so not sure if my answer is what you are looking for.

    I believe that AddItem is designed to adds rows of data to the list box; not columns.

    You can set the number of columns in the Properties when you create the list box in design mode. The column widths start off with a default width but you can also set the column widths in the properties. If setting column widths, start at about 20 wide and then adjust after testing because if you make them too small then you will not see any data in them because they are too narrow.

    The following code will create an array of 10 rows by 13 columns. It populates columns 1 to 12 of the array with random numbers and then sums those numbers and populates column 13 of the array. The array is then assigned to the listbox to populate.

    The example code was tested in the forms Initialize event so that the listbox is populated when the form opens. You can use simialr code anywhere.

    Private Sub UserForm_Initialize()

        Dim arrLstBox(1 To 10, 1 To 13)
        Dim i As Long
        Dim j As Long
        Dim lngSum As Long
      
        'Populate rows 1 to 10 and columns 1 to 12 with random numbers
        For i = 1 To 10
            For j = 1 To 12
                arrLstBox(i, j) = Int((10 * Rnd) + 1)
            Next j
        Next i
       
        'Populate last column with Sum of columns 1 to 12
        For i = 1 To 10
            lngSum = 0
            For j = 1 To 12
                lngSum = lngSum + arrLstBox(i, j)
            Next j
            arrLstBox(i, 13) = lngSum
        Next i
       
        Me.ListBox1.List = arrLstBox()
    End Sub


    Regards, OssieMac

    • Marked as answer by MavilaJay Saturday, September 1, 2012 5:43 PM
    Saturday, September 1, 2012 7:11 AM
  • Thanks OssieMac, but I just get a single column?
    Saturday, September 1, 2012 5:40 PM
  • Ooops, I didn't set the column property to 12.  It works!  Thanks, Ossie!
    Saturday, September 1, 2012 5:43 PM