Solved

Microsoft Excel: Dynamic ListBox on UserForm

Posted on 2011-09-06
7
1,069 Views
Last Modified: 2012-09-18
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
0
Comment
Question by:veematics
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36488063
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
 
LVL 6

Expert Comment

by:TinTombStone
ID: 36488553
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
 

Author Comment

by:veematics
ID: 36493398
@rorya: The idea is to have only 1 userForm with many Dynamic ListBox that generated from certain parameter
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:veematics
ID: 36493427
@TinTombStone: Me is undefined, i'm using option explicit, what the data type of 'Me' ?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36493561
Me refers to the object containing the code - in this case the userform.
0
 
LVL 6

Accepted Solution

by:
TinTombStone earned 500 total points
ID: 36494000
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
 

Expert Comment

by:David Phelops
ID: 38408616
This is really helpful... just what I was looking for this morning.. Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now