+ Reply to Thread
Results 1 to 5 of 5

Combobox Question

  1. #1
    Forum Contributor
    Join Date
    06-14-2013
    Location
    jk
    MS-Off Ver
    Excel 2007
    Posts
    326

    Combobox Question

    Hi All,

    I have a userform with 2 Comboboxes.
    Combobox 1 = Supplier
    Combobox 2 = Product

    Each Supplier has it's own set of products.

    This data is stored in my sheet Data.

    Supplier = Range(Range("B60000").end(xlup),"B3")
    Product = Range(Range("C60000").end(xlup),"C3")

    At this moment my comboboxes contain all items, also duplicate suppliers because there are more than 1 product per supplier.

    I would like to populate Combobox 1 with unique suppliers.
    I would like to populate Combobox 2 with the list of items based on what supplier is chosen in Combobox 1.

    Does anyone have an idea how to?

    Thanks

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Combobox Question

    This article explains how to use pivot tables to get a unique list and cascading values.
    http://www.utteraccess.com/wiki/inde...ists_%28VBA%29

    The article uses the fact that pivot tables know how many articles they "collect" to provide the parameters for the offset command. The sample workbook shows this in action. You can define named ranges and use them to populate the combo boxes.

    http://www.utteraccess.com/wiki/inde...Dynamic_Ranges

    If you have questions, give me a shout. Attach a sample workbook and I'll help you walk through it.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Valued Forum Contributor Maudibe's Avatar
    Join Date
    12-21-2016
    Location
    USA
    MS-Off Ver
    2010-2013
    Posts
    326

    Unique and sorted selections in comboboxes

    Crispy,

    If you don't find your answer in the link that Dflak provided, here is one solution of code where one combobox will populate based on the value of another. The selections in each dropdown will be unique and sorted despite duplication of companies and products in the list.

    In this example there is a "Data" sheet with companies in Column A and the products they sell in column B, and two comboboxes on a userform called CompanyBox and ProductBox. Each company can sell more than one product so they may be listed more than once and each product can be sold by different companies so they can appear more than once also.

    The code uses a dictionary to create unique listings then sorts them using array variables. There is no limit to the number or records and you can add additional comboboxes (levels) using the same technique. Here is the code:

    Copy/paste in the Userform module
    Please Login or Register  to view this content.
    Last edited by Maudibe; 02-03-2017 at 07:54 PM. Reason: Added title

  4. #4
    Valued Forum Contributor Maudibe's Avatar
    Join Date
    12-21-2016
    Location
    USA
    MS-Off Ver
    2010-2013
    Posts
    326

    Re: Combobox Question

    Had to switch browsers to upload file.

    Maud
    Attached Files Attached Files

  5. #5
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Combobox Question

    Another method.

    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

+ 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. ComboBox question
    By Becks7 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-01-2007, 09:42 AM
  2. ComboBox Question
    By Duncan Edment in forum Excel General
    Replies: 1
    Last Post: 08-01-2006, 07:50 AM
  3. ComboBox Question
    By Duncan Edment in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-01-2006, 07:45 AM
  4. ComboBox Question
    By Duncan Edment in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-01-2006, 07:45 AM
  5. [SOLVED] combobox question
    By Bri in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-18-2006, 10:55 AM
  6. [SOLVED] combobox question
    By Bri in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-17-2006, 03:19 PM
  7. ComboBox question
    By the_edge_27 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-16-2005, 04:05 PM
  8. combobox question
    By JT in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-15-2005, 06:45 PM

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