Solved: Populate ListBox from Worksheet Data

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:

[vba]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 [/vba]
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!

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.

Thanks for your input Austen.

I made some changes to the code and got it working. For those who are interested the code is:

[vba]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
[/vba]
I still not quite sure how to approach the dynamic range issue. Will repost once I figure something out.:doh:

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"

The bulk loading of a List box
[vba]ListBox1.List = someRange.Value[/vba] does not work when .ColumnCount > 10.

or maybe

[VBA]ListBox1.List = Range("A:C").Value[/VBA]

This might address your range problem

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.

you should be able to replace this line of your code:

[vba]Set rngSource = Worksheets("Detail").Range("A7:C13")
[/vba]

with either Mickersons or mine.

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!

Quote:

---

Originally Posted by Learner123

.ColumnCount = 50

---

Fifty columns? :bug: This is a typo, yes?

But here you only define three widths.
[vba] 'Set column widths
.ColumnWidths = "50;80;100"[/vba]

Quote:

---

Additionally, the range ("A7:C13") will change over time as a result of rows

---

Add a dynamic range. Formula tab, Define Name. Enter
[vba]=OFFSET(Sheet1!$A$7,0,0,COUNTA(Sheet1!$C:$C),3)[/vba]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.

[vba]
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
[/vba]

In the code above that worked, try changing this:

[vba]Set rngSource = Worksheets("Detail").Range("A7:C13")
[/vba]

to this:

[vba]Set rngSource = Worksheets("Detail").Range("A:C")
[/vba]

disregard my last suggestion.

sometimes trying to do too many things at once is a bad thing: :(

Tinbenders solution is better than what i was suggesting. Less is better.

Thanks!