+ Reply to Thread
Results 1 to 2 of 2

Autofill userform listboxes using a loop

Hybrid View

perducci Autofill userform listboxes... 02-20-2013, 12:51 PM
hafizimran Re: Autofill userform... 02-20-2013, 01:48 PM
  1. #1
    Registered User
    Join Date
    10-06-2011
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    78

    Autofill userform listboxes using a loop

    I have a userform with 40 listboxes. I use the following code to fill one listbox using a list of values in an excel sheet:

    lrow = Sheets("Listboxes").Cells(Sheets("Listboxes").Rows.Count, 1).End(xlUp).Row
        Userform1.ListBox1.Clear
        For i = 2 To lrow
        With Userform1.ListBox1
            .AddItem Sheets("Listboxes").Cells(i, 1)
        End With
        Next i
    I want to add another loop to this to fill all the listboxes and say something like this:

    Dim i as integer
    dim j as integer
    dim lrow as integer
    
    For j = 1 to 40
    lrow = Sheets("Listboxes").Cells(Sheets("Listboxes").Rows.Count, 1).End(xlUp).Row
        Userform1.ListBox(j).Clear
        For i = 2 To lrow
        With Userform1.ListBox(j)
            .AddItem Sheets("Listboxes").Cells(i, 1)
        End With
        Next i
    Next j
    Any ideas how to make this work?

    Thanks in advance

  2. #2
    Forum Contributor
    Join Date
    12-31-2012
    Location
    Jhang, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    250

    Re: Autofill userform listboxes using a loop

    First of all you need to name the list-boxes as Listbox1, Listbox2.... Listbox40

    then change your code like this:
    Dim i as integer
    dim j as integer
    dim lrow as integer
    
    For j = 1 to 40
    lrow = Sheets("Listboxes").Cells(Sheets("Listboxes").Rows.Count, 1).End(xlUp).Row
        Userform1.Controls("Listbox" & j).Clear
        For i = 2 To lrow
        With Userform1.Controls("Listbox" & j)
            .AddItem Sheets("Listboxes").Cells(i, 1)
        End With
        Next i
    Next j

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1