Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Microsoft Excel: Dynamic ListBox on UserForm

Posted on 2011-09-06
7
Medium Priority
?
1,310 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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

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.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

577 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