Solved

Microsoft Excel: Dynamic ListBox on UserForm

Posted on 2011-09-06
7
1,048 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

760 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

18 Experts available now in Live!

Get 1:1 Help Now