Microsoft Excel: Dynamic ListBox on UserForm

Hello,
i'm bit newbie in Forms with VBA's on Excel. There are several thing i would like to ask:
1. How to add/create ListBox in UserForm with VBA ? i'm thinking to do it on initialize event
2. How to populate the RowSource Dynamically with VBA ? right now i'm manually building it, but the problem arise when i use A2:A65536 , it build a lot of empty spaces.
listbox.jpg
veematicsAsked:
Who is Participating?
 
TinTombStoneConnect With a Mentor Commented:
OK, this will generate multiple ListBoxes, one for each column (up to six in this example, change the const for more) in the activesheet

sure it could be neater/quicker, give it a go
dropped on an example file to (.xls)
 ListBox-Example-2.xls
Private Sub UserForm_Initialize()

Dim listNew() As Control
Dim labelsNew() As Control
Dim numBoxes As Integer
Dim lstCount As Integer
Const maxCols As Integer = 6 'limits the number of boxes generated

    'Generate list boxes based on column count
    numBoxes = Range("A1").CurrentRegion.Columns.Count
    
    'exit is no data
    If numBoxes = 0 Then Exit Sub
    'set maximum number of boxes
    If numBoxes > maxCols Then numBoxes = maxCols
    'resize control arrays
    ReDim listNew(1 To numBoxes)
    ReDim labelsNew(1 To numBoxes)
    
    'set up first listbox on column 1
    Set listNew(1) = Me.Controls.Add("Forms.ListBox.1", "lstSample1" & lstCount, True)
    With listNew(1)
        .Left = 10
        .Top = 55
        .Width = 100
        .Height = 150
        .RowSource = Range(Cells(2, 1), Cells(Cells(2, 1).CurrentRegion.Rows.Count, 1)).Address
    End With
    
    'set up first label from A1
    Set labelsNew(1) = Me.Controls.Add("Forms.Label.1", "lstLabel1" & lstCount, True)
    With labelsNew(1)
        .Left = 10
        .Top = 40
        .Width = 100
        .Height = 20
        .Caption = Cells(1, 1).Value
    End With
    
    'loop over remaining columns
    For lstCount = 2 To numBoxes
        
        'add listbox for column lstCount
        Set listNew(lstCount) = Me.Controls.Add("Forms.ListBox.1", "lstSample" & lstCount, True)
        With listNew(lstCount)
            .Left = listNew(lstCount - 1).Left + 110
            .Top = 55
            .Width = 100
            .Height = 150
            .RowSource = Range(Cells(2, lstCount), Cells(Cells(2, lstCount).CurrentRegion.Rows.Count - 1, lstCount)).Address
        End With
        
        'add Label for column lstCount
        Set labelsNew(lstCount) = Me.Controls.Add("Forms.Label.1", "lstLabel" & lstCount, True)
        With labelsNew(lstCount)
            .Left = listNew(lstCount - 1).Left + 110
            .Top = 40
            .Width = 100
            .Height = 20
            .Caption = Cells(1, lstCount).Value
        End With
    Next lstCount
    
    'resize form
    Me.Width = 115 * numBoxes
    Me.Height = 250
End Sub

Open in new window

0
 
Rory ArchibaldCommented:
Why do you want to add the listbox at run time? Why not design time?

Personally, I never use RowSource, I always use List:

Me.Listbox1.List = Sheets("Sheet1").range("A2", Sheets("Sheet1").Cells(rows.count, "A").End(xlup)).Value

Open in new window

0
 
TinTombStoneCommented:
The following code creates a listbox on a form then loads it with values from a named range
Private Sub UserForm_Initialize()

Dim listNew As Control

Set listNew = Me.Controls.Add("Forms.ListBox.1", "lstSample", True)

    With listNew
        .Left = 10
        .Top = 30
        .Width = 100
        .Height = 120
        
        .RowSource = Range("ListValues").Address
    End With
    
End Sub

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
veematicsAuthor Commented:
@rorya: The idea is to have only 1 userForm with many Dynamic ListBox that generated from certain parameter
0
 
veematicsAuthor Commented:
@TinTombStone: Me is undefined, i'm using option explicit, what the data type of 'Me' ?
0
 
Rory ArchibaldCommented:
Me refers to the object containing the code - in this case the userform.
0
 
David PhelopsCommented:
This is really helpful... just what I was looking for this morning.. Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.