+ Reply to Thread
Results 1 to 7 of 7

Dynamic dropdown list selection based on combo box

  1. #1
    Valued Forum Contributor
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    513

    Dynamic dropdown list selection based on combo box

    Hi there,

    I am trying to understand how the Replace() part works. Operationally, it is a dynamic dropdown list based on combo boxes on a user form.

    Please Login or Register  to view this content.
    How does the replace() part work? and what does the .listindex > -1 represent?

    thanks
    Attached Files Attached Files
    Last edited by Lifeseeker; 02-10-2012 at 10:55 AM.

  2. #2
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Dynamic dropdown list selection based on combo box

    ListIndex returns the item that is selected (0 is the first item, 1 the second and so on) or -1 if no item has been chosen.

    The data for the list is coming from named ranges, which cannot have spaces in the name, so they have been named using _ instead. The combobox contains the names with spaces, the replace changes those for _ to match the actual names and then assigns that to the list.

    Have not looked at the file, but if you had say 'Named Range 1' in the list, the actual range name would be 'Named_Range_1' and that is what the Replace function converts to.
    Good luck.

  3. #3
    Valued Forum Contributor
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    513

    Re: Dynamic dropdown list selection based on combo box

    Helpful. Right, the named ranges cannot have space in them.

    I have one follow-up question:

    this is in the name manager.

    Please Login or Register  to view this content.
    What exactly does the offset and counta() do altogether? Can't you just select the range manually?

  4. #4
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Dynamic dropdown list selection based on combo box

    You can, but the point of this is that it will automatically expand as you add data in column C.
    It basically says start from C2 and then use as many rows as have data in C2:C100.

  5. #5
    Valued Forum Contributor
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    513

    Re: Dynamic dropdown list selection based on combo box

    can you do this instead?

    Please Login or Register  to view this content.

  6. #6
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Dynamic dropdown list selection based on combo box

    Yes, as long as there is something in C1 on the sheet. My preference would actually be for

    =Sheet1!$C$2:INDEX(Sheet1!$C$:$C$,COUNTA(Sheet1!$C$:$C$))

  7. #7
    Valued Forum Contributor
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    513

    Re: Dynamic dropdown list selection based on combo box

    great, I will try it out sometimes

+ 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1