• 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.
  • MVP Logo
  • Recent Forum Posts

    p45cal

    Complex counting help

    The table in the latest file needs refreshing - I should have done that myself - sorry....

    p45cal Yesterday, 05:45 PM Go to last post
    Mr_E_Man

    Complex counting help

    Hi p45cal - I think there is still some error in the logic but your suggestion of using Power Query has been invaluable. I have been researching that...

    Mr_E_Man Yesterday, 05:35 PM Go to last post
    p45cal

    Complex counting help

    Attached adjusted accordingly....

    p45cal 2020-08-04, 11:31 PM Go to last post
    Mr_E_Man

    Complex counting help

    Hi p45cal - the specific "DT" function is the only one that does not get counted for the keypad on the 2nd or 3rd reference, it is different...

    Mr_E_Man 2020-08-04, 05:55 PM Go to last post
    p45cal

    Complex counting help

    The attached contains a table at cell I6, which agrees with your totals except for the keypad count of the first site (1185) where I have counted the...

    p45cal 2020-08-04, 03:05 PM Go to last post