Consulting

Results 1 to 14 of 14

Thread: Solved: Populate ListBox from Worksheet Data

  1. #1

    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!

  2. #2
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,009
    Location
    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.
    Peace of mind is found in some of the strangest places.

  3. #3
    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.

  4. #4
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,009
    Location
    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"
    Peace of mind is found in some of the strangest places.

  5. #5
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,776
    The bulk loading of a List box
    [vba]ListBox1.List = someRange.Value[/vba] does not work when .ColumnCount > 10.

  6. #6
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,009
    Location
    or maybe

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

    This might address your range problem
    Peace of mind is found in some of the strangest places.

  7. #7
    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.

  8. #8
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,009
    Location
    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.
    Peace of mind is found in some of the strangest places.

  9. #9
    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!

  10. #10
    VBAX Expert Tinbendr's Avatar
    Joined
    Jun 2005
    Location
    North Central Mississippi (The Pines)
    Posts
    991
    Location
    Quote Originally Posted by Learner123
    .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]

  11. #11
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,009
    Location
    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.
    Peace of mind is found in some of the strangest places.

  12. #12
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,009
    Location
    sometimes trying to do too many things at once is a bad thing:
    Peace of mind is found in some of the strangest places.

  13. #13
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,009
    Location
    Tinbenders solution is better than what i was suggesting. Less is better.
    Peace of mind is found in some of the strangest places.

  14. #14
    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •