Results 1 to 3 of 3

Thread: Article: Fill MultiColumn Listbox With Worksheet Range

  1. #1
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    Excel Office 365 Insider

    Article: Fill MultiColumn Listbox With Worksheet Range

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!

    Main Site: -||- Blog: -||- Forums:
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  2. #2
    Neophyte Kubiszynb's Avatar
    Join Date
    Apr 2013

    Nice examples

    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:

    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

  3. #3
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts