• Fill MultiColumn Listbox With Worksheet Range

    Introduction:
    This article gives two methods to fill a multi column listbox on a userform with data from an Excel worksheet.
    • Pulling the worksheet range directly into a listbox
    • Pulling the data into an array first, then placing the array in the listbox

    The intention of both of these pieces of code is to be used in a userform. Both are set up in the Userform_Intialize event, although only one or other should be used.

    Versions Tested:
    These routines were tested successfully running Office 2003, but shouldn't have any issue in Excel 97 or higher.

    Pulling the worksheet range directly into a listbox
    Code:
    Private Sub UserForm_Initialize()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: To populate a multi-column listbox with data from
    '               a worksheet range
        
        Dim lbtarget As MSForms.ListBox
        Dim rngSource As Range
        
        'Set reference to the range of data to be filled
        Set rngSource = Worksheets("Sheet1").Range("A2:C5")
        
        'Fill the listbox
        Set lbtarget = Me.ListBox1
        With lbtarget
            'Determine number of columns
            .ColumnCount = 3
            'Set column widths
            .ColumnWidths = "50;80;100"
            'Insert the range of data supplied
            .List = rngSource.Cells.Value
        End With
        
    End Sub
    Pulling the data into an array first, then placing the array in the listbox
    Code:
    Private Sub UserForm_Initialize()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: To populate a multi-column listbox with data from
    '               a worksheet range
        Dim lb As msforms.ListBox
        Dim rcArray() As Variant
        Dim lrw As Long, lcol As Long
        Dim rngTarget As Range
        
        'Define the range you want to use
        Set rngTarget = Worksheets("Sheet1").Range("A2:C5")
        
        'Set the boundaries of the array
        ReDim Preserve rcArray(1 To rngTarget.Rows.Count, 1 To rngTarget.Columns.Count)
        
        'Fill the array with data from the worksheet
        With rngTarget
            For lcol = 1 To .Columns.Count
                For lrw = 1 To .Rows.Count
                    rcArray(lrw, lcol) = rngTarget.Cells(lrw, lcol)
                Next lrw
            Next lcol
        End With
        
        'Place the array in the listbox
        Set lb = Me.ListBox1
        With lb
            .ColumnCount = 3
            .ColumnWidths = "50;80;100"
            .List = rcArray
        End With
        
    End Sub
    Example File:

     

    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

     

    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

    Comments 2 Comments
    1. Kubiszynb's Avatar
      Kubiszynb -
      Nice examples. Just thought I would mention that you can pick up & hide Column data by setting the Column Width to 0. You can also pull Worksheet data directly into a Variant array, so using your Code:

      Code:
      Private Sub UserForm_Initialize()
      
          Dim lb As msforms.ListBox
          
          ' // alternative method to pick up Worksheet data
          Dim ar As Variant
          ar = Worksheets("Sheet1").Range("A2:C5").Cells
          
          'Place the array in the listbox
          Set lb = Me.ListBox1
          With lb
              .ColumnCount = 3
              
              ' // pick up & hide the first Column of data
              .ColumnWidths = "0;80;100"
              .List = ar
          End With
          
      End Sub
      Cheers, Mark
    1. Kprajath's Avatar
      Kprajath -
      Thanks Mr.Ken, the post was really good. Requesting to kindly give some more knowledge on how to control the range of list box for normal screen display (Not user forms). On a screen for display of some Company Data, i select name of the Company and various info are displayed alongwith list of Suppliers. In some cases, the list of Suppliers may change as per additions/deletions. i need to display this information in the listbox from row 1 to actual last row of the sheet for suppliers list. Thanks in advance.