Results 1 to 12 of 12

Thread: [RESOLVED] [Excel] How do you Load data into a listbox?

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2001
    Location
    Washington DC
    Posts
    330

    Resolved [RESOLVED] [Excel] How do you Load data into a listbox?

    I have a list box that I want to load with more than one column of data.
    I have this, which only populates the list box with one column of data. How do I adjust it to add an entire range. I tried to change the range to A2:M2 but that didnt work. I tried to look in help for an explaination of the Offset property, but help isnt working for me, it returns not found.


    Private Sub UserForm_Initialize()
    Range("A2").Select
    Do Until ActiveCell.Value = ""
    lstPlanItems.AddItem CStr(ActiveCell.Value)
    ActiveCell.Offset(1, 0).Select
    Loop
    Range("A2").Select
    End Sub

    Thank you.
    Swoozie
    Somedays you just should not get out of bed.

  2. #2
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: [Excel] How do you Load data into a listbox?

    Hi Swoozie

    You can achieve that by setting the ColumCount Property to 13 (Col A to Col M). You can do it at design time or you can do it in runtime, whichever is convenient to you

    ListBox1.ColumnCount = 13

    Hope this helps...
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2001
    Location
    Washington DC
    Posts
    330

    Re: [Excel] How do you Load data into a listbox?

    I did set the column count to 13, and I sized the columns. I did this in the properties of the list box. But I still only see the 1st column
    Swoozie
    Somedays you just should not get out of bed.

  4. #4
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: [Excel] How do you Load data into a listbox?

    Check for these....

    1) Did you set the range?. I mean did you set the ListFillRange property as Sheet1!A2:M2? (note: replace sheet1 with the name of your sheet and A2:M2 with the relevant range)

    2) Did you resize the listbox?
    3) Do you see a scroll bar?
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2001
    Location
    Washington DC
    Posts
    330

    Re: [Excel] How do you Load data into a listbox?

    The code I had is pasted above, but here it is again. I tried to change the range to A2:M2" but that didnt work.

    Code:
    Private Sub UserForm_Initialize()
    Range("A2").Select
    Do Until ActiveCell.Value = ""
    lstPlanItems.AddItem CStr(ActiveCell.Value)
    ActiveCell.Offset(1, 0).Select
    Loop
    Range("A2").Select
    End Sub
    Swoozie
    Somedays you just should not get out of bed.

  6. #6
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: [Excel] How do you Load data into a listbox?

    In Desingtime, Right click the control to access the properties.

    under the ListFillRange property type Sheet1!A2:M2 or Sheet1!A1:M1 as done in the pic below...

    Hope this helps...
    Attached Images Attached Images  
    Last edited by Siddharth Rout; Sep 4th, 2007 at 03:28 PM.
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  7. #7
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: [Excel] How do you Load data into a listbox?

    And if I modify your code then this will work

    Code:
    Private Sub CommandButton2_Click()
    
    Range("A2").Select
    Do Until ActiveCell.Value = ""
    lstPlanItems.AddItem ActiveCell.Value
    ActiveCell.Offset(0, 1).Select  <= DIFFERENCE IS HERE!!!!
    Loop
    Range("A2").Select
    
    End Sub
    Note:
    1) When you move horizontly, then it's Offset(0, 1)
    2) When you move verticaly, then it's Offset(1, 0)

    Hope this helps...
    Last edited by Siddharth Rout; Sep 4th, 2007 at 03:26 PM.
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  8. #8

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2001
    Location
    Washington DC
    Posts
    330

    Re: [Excel] How do you Load data into a listbox?

    I do not have a ListFillRange property type in the list box properties.

    Also, I want each row of the table to fill each row in the listbox so I don't think I can use really any of this code that I started with, because I want to use a range not a cell.

    But the really scary part is that i can update the text boxes with the values I can not see in the tables. This code works.

    Code:
        Me.txtStepNum.Text = CStr(ActiveCell.Offset(lstPlanItems.ListIndex, 0).Value)
        Me.cboProjectCategory.Text = CStr(ActiveCell.Offset(lstPlanItems.ListIndex, 1).Value)
    Swoozie
    Somedays you just should not get out of bed.

  9. #9
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: [Excel] How do you Load data into a listbox?

    Quote Originally Posted by swoozie
    I do not have a ListFillRange property type in the list box properties.
    THIS I NEED TO SEE MYSELF

    Can you please upload your file???
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  10. #10

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2001
    Location
    Washington DC
    Posts
    330

    Re: [Excel] How do you Load data into a listbox?

    No, I can not upload , but the last code item I used using the rowsource was

    Code:
    Private Sub UserForm_Initialize()
    'populate all listboxes
    Dim c As Control
    
    'this is just code I got from a tutorial, it really is unneeded in ths application
    'but good for future reference
    
        For Each c In Me.Controls
            'format the listboxes
            If TypeOf c Is MSForms.ListBox Then
                c.ColumnCount = 13
                c.ColumnWidths = "50;50;50;50;50;50;50;50;50;50;50;50;50"
            End If
        Next c
        
        'I have no idea if this is required, but It seems that it should be
        Worksheets("Plan").Activate
        'Insert Code here to find the end of the range
        
        'Populate the listbox via the rowsource
        With Me.lstPlanItems
            .RowSource = "A2:M244"
        End With
    
    End Sub
    Populate the corresponding text boxes

    Code:
        Me.txtStepNum.Text = CStr(ActiveCell.Offset(lstPlanItems.ListIndex, 0).Value)
    Thank you
    Swoozie
    Somedays you just should not get out of bed.

  11. #11
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: [Excel] How do you Load data into a listbox?

    Oh, Ok

    Before I paste the code lemme confirm my understanding...

    1) The Listbox is in a userform in Excel 2003
    2) you want to populate data from say three columns into the listbox which is in say sheet1 and
    3) You want to do this via code for all listboxes in that form...

    If yes, then this should help...

    Code:
    Private Sub CommandButton1_Click()
    Dim c As Control
        For Each c In Me.Controls
            'format the listboxes
            If TypeOf c Is MSForms.ListBox Then
                'IF you want data from say three columns (A,B and C)
                c.ColumnCount = 3
                c.RowSource = "Sheet1!A1:C1"
                c.Width = 577.5
            End If
        Next c
    End Sub
    Hope this helps...
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  12. #12

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2001
    Location
    Washington DC
    Posts
    330

    Re: [Excel] How do you Load data into a listbox?

    Close, I guess i am just not quite clear enough.

    I want to populate 1 listbox that has 13 columns (if possible)
    Then I want the user to be able to click on the item in the list box to populate textboxes for editing.

    for some reason, my code doesnt work. If I populate only the first column to the list box, then I can populate the text boxes, If i load the listboc, then I can't populate the textboxes.

    What I really would like is something like access uses where you query the data, update the list box, from the query. So if the user only wants to see items that are black, they can select that.
    Swoozie
    Somedays you just should not get out of bed.

Posting Permissions

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



Click Here to Expand Forum to Full Width