+ Reply to Thread
Results 1 to 6 of 6

How to insert a dynamic range in a UserForm into a ListBox

  1. #1
    Forum Contributor stojko89's Avatar
    Join Date
    05-18-2009
    Location
    Maribor, Slovenia
    MS-Off Ver
    MS Office 365
    Posts
    913

    How to insert a dynamic range in a UserForm into a ListBox

    Hi

    I made a UserForm that collects the data from the active sheet and it copys it in to the UserForm.

    In here I have a ListBox where in the options menu I set the ColumnCount: 6 becouse I have 6 rows I want to be shown in the UserForm. The Columns are A:F how many rows is actualy different. Sometime the data goes from A3:F40 and sometimes it goes from A3:F5 depends on the material order. Under options for the ListBox I tryed to enter the RowSource but I can only insert it like this A3:F40 then I have a lot of empty cells.

    So is it possible somehow to insert here a dynamic range that would take cells from A3 (always the beggining cell) to F - How many rowes are filled?
    Last edited by stojko89; 11-18-2011 at 03:27 AM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: How to insert a dynamic range in a UserForm into a ListBox

    Create a proper Dynamic Named Range to use as the RowSource

    Alternatively, you can set the range when the form loads

    Please Login or Register  to view this content.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Contributor stojko89's Avatar
    Join Date
    05-18-2009
    Location
    Maribor, Slovenia
    MS-Off Ver
    MS Office 365
    Posts
    913

    Re: How to insert a dynamic range in a UserForm into a ListBox

    royUK
    I've inserted the code like this:
    Please Login or Register  to view this content.
    And I get an error and it says that it couldn't set the Value property.

  4. #4
    Forum Contributor stojko89's Avatar
    Join Date
    05-18-2009
    Location
    Maribor, Slovenia
    MS-Off Ver
    MS Office 365
    Posts
    913

    Re: How to insert a dynamic range in a UserForm into a ListBox

    Sory!! Sory!!

    I just saw I've entered the last part of the code wrong.

    It works great! :D

  5. #5
    Forum Contributor stojko89's Avatar
    Join Date
    05-18-2009
    Location
    Maribor, Slovenia
    MS-Off Ver
    MS Office 365
    Posts
    913

    Re: How to insert a dynamic range in a UserForm into a ListBox

    Thanks a lot royUK!

    Works for every sheet

    I won't even bother to try and make the dynamic range the other way.

    So thank you.

  6. #6
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: How to insert a dynamic range in a UserForm into a ListBox

    or

    Please Login or Register  to view this content.
    If you treat these sheets as data'warehouses' you can start in row 1.
    Last edited by snb; 11-18-2011 at 03:30 AM.



+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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