+ Reply to Thread
Results 1 to 4 of 4

VBA code to add range to list box

  1. #1
    Registered User
    Join Date
    04-02-2014
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    79

    VBA code to add range to list box

    Hi,

    Please help me with the VBA code to add a list to list box
    I don't have a user form. it has to be in a excel sheet just like the attachment.
    list box is in sheet 1 and range in sheet 2.
    list range may increase or decrease.
    Need a VBA code so that the list (excluding blanks) automatically gets added to this list box in sheet 1 whenever the user needs to.

    thanks.
    Shree.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,540

    Re: VBA code to add range to list box

    Create a Dynamic Named Range of the Sheet2 list.
    Use this as the Refers to formula for the Named Range
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then use the Named Range as the Input Range for the drop down list.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    04-02-2014
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: VBA code to add range to list box

    Hi Alphafrog, this works well. but when i clear the contents of sheet 2 to add a new range of list, the list box looses its input range (Sheet2!#REF!)
    I need the list list box to carry the input range whatever happens to the contents of sheet 2. is it possible?

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,540

    Re: VBA code to add range to list box

    Don't delete the rows or column on Sheet2. Just clear the cell contents.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Condensing a list/range with blank cells to a new list/range without blanks
    By Duke Carey in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 12:05 PM
  2. [SOLVED] Condensing a list/range with blank cells to a new list/range without blanks
    By KR in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  3. Condensing a list/range with blank cells to a new list/range without blanks
    By Duke Carey in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 04:05 AM
  4. Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  5. [SOLVED] Condensing a list/range with blank cells to a new list/range without blanks
    By KR in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM

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