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:

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]

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:

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]

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
ListBox1.List = someRange.Value does not work when .ColumnCount > 10.

[austenr]

or maybe

ListBox1.List = Range("A:C").Value

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:

Set rngSource = Worksheets("Detail").Range("A7:C13")


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 = 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]

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]

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!