Solved: Populate ListBox from Worksheet Data

[Learner123]

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!

[austenr]

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]

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.

[austenr]

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]

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

[austenr]

or maybe

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

This might address your range problem

[Learner123]

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]

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.

[Learner123]

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]

.ColumnCount = 50

Fifty columns? This is a typo, yes?

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

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]

[austenr]

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.

[austenr]

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

[austenr]

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

[Learner123]

Thanks!