+ Reply to Thread
Results 1 to 5 of 5

Combo box unique entries

  1. #1
    Registered User
    Join Date
    11-29-2012
    Location
    Oxford
    MS-Off Ver
    Excel 2016
    Posts
    8

    Combo box unique entries

    Hi

    I am looking for a way to populate a user form combo box with only unique entries from a table. Any help would be greatly appreciated!

    test.jpg

    Regards, Steve
    Attached Files Attached Files

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Combo box unique entries

    We kind-of need more info. For instance...
    1) What entries do you want in the ComboBox?
    2) What determines which entries go into the ComboBox and when?
    Attaching the workbook is great, however, when I open the UserForm, the ComboBox in the UserForm shows exactly what you have in the image.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Registered User
    Join Date
    11-29-2012
    Location
    Oxford
    MS-Off Ver
    Excel 2016
    Posts
    8
    Quote Originally Posted by gmr4evr1 View Post
    We kind-of need more info. For instance...
    1) What entries do you want in the ComboBox?
    2) What determines which entries go into the ComboBox and when?
    Attaching the workbook is great, however, when I open the UserForm, the ComboBox in the UserForm shows exactly what you have in the image.
    Apologies, so my idea was that for any new item you can manually type a new location or supplier or you can select a previously used location or supplier that has been entered previously, my only issue is the combo box displays the whole location and supplier columns whereas i only want it to supply unique entries. For example in the image the combo box displays fridge 1 twice because it appears on the location column twice, i only want it to apear in the list once. Hope that makes sense and thanks for the reply!

    Regards, Steve

  4. #4
    Registered User
    Join Date
    01-22-2016
    Location
    Albany, NY
    MS-Off Ver
    2010
    Posts
    31

    Re: Combo box unique entries

    I'm a beginner at Excel so I assume some of the experts on here might have a simpler solution, but this might help (I assume that the values in the Location column are sorted in alphabetical order):


    Please Login or Register  to view this content.
    Set Location = .Range("FridgeTable[Location]")


    ' Add 1st value of Location range to combo box
    ComboBox1.AddItem (Location(1))

    ' Set 1st value in Location to oldVal so we can check for duplicates in the loop
    oldVal = Location(1)

    ' Loop through each Cell in Location range and add only unique values to the combobox
    For i = 2 To Location.Count
    newVal = Location(i)

    ' Check if cell's value is a duplicate. If it is, don't add it to the combobox
    If newVal <> oldVal Then

    ' Add value to combobox
    ComboBox1.AddItem (newVal)

    ' Set current Cell value to oldVal so we can check for duplicates in the next
    ' iteration of the loop
    oldVal = newVal
    End If

    Next
    Please Login or Register  to view this content.
    Last edited by JaguerRhye; 02-17-2016 at 01:04 PM.

  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: Combo box unique entries

    Remove RowSource from both Comboboxes.
    Please Login or Register  to view this content.

+ 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. Generating a unique list of entries but removing some entries.
    By Kramxel in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-14-2015, 11:42 AM
  2. [SOLVED] Count unique entries based off unique criteria in another column
    By hambly in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-11-2014, 03:33 PM
  3. Code to populate combo box with unique entries from column (in alphabetical order)
    By Nitefox in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 34
    Last Post: 01-26-2014, 03:05 AM
  4. Replies: 7
    Last Post: 05-11-2012, 02:00 PM
  5. Using VBA: I have a list with multiple entries.Need to reduce down to unique entries
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-25-2011, 03:19 PM
  6. Replies: 4
    Last Post: 05-19-2011, 01:06 PM
  7. Duplicate Entries into List of Unique Entries
    By MjRmatt in forum Excel General
    Replies: 1
    Last Post: 08-18-2010, 11:53 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