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
    Location
    Nanaimo, BC, Canada
    Posts
    2,027
    Articles
    79
    Blog Entries
    14

    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 (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/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
    Location
    Northampton
    Posts
    1
    Articles
    0

    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:

    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
  •