View Full Version : Solved: Populate ListBox from Worksheet Data
Learner123
11-15-2010, 10:57 AM
Hi again,
I was hoping to populate list boxes based on the data entered onto a worksheet.
The code that I was able to retrieve is as follows:
Private Sub UserForm2_Initialize()
Dim lbtarget As MSForms.ListBox
Dim rngSource As Range
'Set reference to the range of data to be filled
Set rngSource = Worksheets("Detail").Range("A7:C13")
'Fill the listbox
Set lbtarget = Me.ListBox1
With lbtarget
'Determine number of columns
.ColumnCount = 50
'Set column widths
.ColumnWidths = "50;80;100"
'Insert the range of data supplied
.List = rngSource.Cells.Value
End With
End Sub
However, the listBox will not populate when I run the code. Can someone help determine where the error lies?
Additionally, the range ("A7:C13") will change over time as a result of rows being added. Is there away to make the range automatically adapt? Will naming the range correct this issue?
As always, thanks for your time!
austenr
11-15-2010, 11:25 AM
Well to start with you have no object named ListBox1.
Secondly, if your rows are going to change over time, you need to use dynamic ranges. Lots of exam[ples of those on the board.
Learner123
11-15-2010, 11:58 AM
Thanks for your input Austen.
I made some changes to the code and got it working. For those who are interested the code is:
Private Sub UserForm_Initialize()
Dim lbtarget As MSForms.ListBox
Dim rngSource As Range
'Set reference to the range of data to be filled
Set rngSource = Worksheets("Detail").Range("A7:C13")
'Fill the listbox
Set lbtarget = Me.ListBox1
With lbtarget
'Determine number of columns
.ColumnCount = 50
'Set column widths
.ColumnWidths = "50;80;100"
'Insert the range of data supplied
.List = rngSource.Cells.Value
End With
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
I still not quite sure how to approach the dynamic range issue. Will repost once I figure something out.:doh:
austenr
11-15-2010, 12:07 PM
Glad to point a few things out. If your question is answered satisfactorly then please mark it solved. Go right under your initial post and you will see a thread tools drop down. Select "Mark Thread Solved" the click "Perform Action"
mikerickson
11-15-2010, 12:22 PM
The bulk loading of a List box
ListBox1.List = someRange.Value does not work when .ColumnCount > 10.
austenr
11-15-2010, 12:45 PM
or maybe
ListBox1.List = Range("A:C").Value
This might address your range problem
Learner123
11-15-2010, 01:03 PM
Thank you both for the additional code!
Can someone clarify where exactly it needs to be inserted? I am not sure how to apply it.
austenr
11-15-2010, 01:32 PM
you should be able to replace this line of your code:
Set rngSource = Worksheets("Detail").Range("A7:C13")
with either Mickersons or mine.
Learner123
11-15-2010, 01:39 PM
In following your directions, I recieved an error message "Run-time '91': Pbject variable or With block variable not set".
Am I doing something wrong?
Thanks for all your patience!
Tinbendr
11-15-2010, 01:48 PM
.ColumnCount = 50Fifty columns? :bug: This is a typo, yes?
But here you only define three widths.
'Set column widths
.ColumnWidths = "50;80;100"
Additionally, the range ("A7:C13") will change over time as a result of rows
Add a dynamic range. Formula tab, Define Name. Enter
=OFFSET(Sheet1!$A$7,0,0,COUNTA(Sheet1!$C:$C),3)Now, the rows will be dynamic.
Whatever name you give the range, enter that into the RowSource on the listbox.
Reduce the code will look like this.
Private Sub UserForm2_Initialize()
Dim lbtarget As MSForms.ListBox
'Fill the listbox
Set lbtarget = Me.ListBox1
With lbtarget
'Determine number of columns
.ColumnCount = 3
'Set column widths
.ColumnWidths = "50;80;100"
End With
End Sub
austenr
11-15-2010, 01:49 PM
In the code above that worked, try changing this:
Set rngSource = Worksheets("Detail").Range("A7:C13")
to this:
Set rngSource = Worksheets("Detail").Range("A:C")
disregard my last suggestion.
austenr
11-15-2010, 01:50 PM
sometimes trying to do too many things at once is a bad thing: :(
austenr
11-15-2010, 02:13 PM
Tinbenders solution is better than what i was suggesting. Less is better.
Learner123
11-17-2010, 11:38 AM
Thanks!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.