Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Microsoft Excel: Dynamic ListBox on UserForm

Posted on 2011-09-06
7
Medium Priority
?
1,258 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

636 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