+ Reply to Thread
Results 1 to 12 of 12

Populating listbox with unique values

  1. #1
    Registered User
    Join Date
    08-12-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    25

    Populating listbox with unique values

    Hello everyone,
    Can you tell me how to add unique values in the Active X listbox.
    This is my code :

    Please Login or Register  to view this content.
    But its giving me some error
    I have data in sheet2 from which i want to populate 4 dropdowns in sheet3 but i want to insert unique values only, so can you plz tell me how to do it.
    Thanks.

  2. #2
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Populating listbox with unique values

    Please Login or Register  to view this content.

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

    Re: Populating listbox with unique values

    Never use 'additem' to populate a combobox/listbox:

    Please Login or Register  to view this content.
    If necessary adapt this code e.g. to A1:A4000



  4. #4
    Registered User
    Join Date
    08-12-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: Populating listbox with unique values

    Quote Originally Posted by Bob Phillips View Post
    Please Login or Register  to view this content.
    Thanks a lot...
    Is there only 2 peoples allowed to reply in here :-P

  5. #5
    Registered User
    Join Date
    08-12-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: Populating listbox with unique values

    Quote Originally Posted by snb View Post
    Never use 'additem' to populate a combobox/listbox:

    Please Login or Register  to view this content.
    If necessary adapt this code e.g. to A1:A4000
    Thanks for the reply and sure i will surely follow your advice, i am new to excel programming so i really need such advices
    Thanks a lot once again.

  6. #6
    Registered User
    Join Date
    08-12-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: Populating listbox with unique values

    I am confused with 2 things -
    1. I am unable to save the contents of the listbox.
    2. I gets overflow error, when i apply for loop like this

    For i = 1 To FinalRow

    Here FinalRow = 62500
    Listbox cannot handle these many records ??

  7. #7
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Populating listbox with unique values

    Quote Originally Posted by protocol View Post
    I am confused with 2 things -
    1. I am unable to save the contents of the listbox.
    2. I gets overflow error, when i apply for loop like this

    For i = 1 To FinalRow

    Here FinalRow = 62500
    Listbox cannot handle these many records ??
    Then you need to re-think your design. How can you possibly examine a list with 60,000+ items?

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

    Re: Populating listbox with unique values

    Please do not quote (see the forum rules).
    You won't get an overflow message using my code suggestion.

    An alternative method:

    Please Login or Register  to view this content.
    Last edited by snb; 08-17-2011 at 05:43 AM.

  9. #9
    Registered User
    Join Date
    08-12-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: Populating listbox with unique values

    @Bob
    Thanks for the reply

    @Snb
    Thanks for the code, i will try and post the result.Thanks a lot.

  10. #10
    Registered User
    Join Date
    08-12-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: Populating listbox with unique values

    @Snb
    Can you plz explain a little bit about this code

    ComboBox1.List = Filter([transpose(IF(countif(Offset($A$1,,,ROW(A1:A200)),A1:A200)=1,A1:A200,"#"))], "#", False)

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

    Re: Populating listbox with unique values

    It creates a new 1-dimensional array based on
    the frequency (countif) of the value A1,A2,A3,A4..A200 etc. in range A1:A1, A1:A2,A1:A3,A1:A4...A1:A200 (offset(A1,,,row(A1,A200)) respectively.
    If the frequency is 1 then the value will be put into the array, if the frequency is higher a # will be put into the array.
    The array will be turned into a 1-dimensional array using 'transpose', so that we can filter all items unlike #.
    Because a comboboxlist is an array we can assign that array to the comboboxlist.

  12. #12
    Registered User
    Join Date
    08-12-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: Populating listbox with unique values

    @Bob your code is giving me error on this line :

    EDIT : Its working now, thanks a lot.. + rep for u again.


    ListBox123.List = vecList

    and it works fine and it filtered the list which contains multiple managers, admin, it filtered them and i got 1 manager and 1 admin but it also inserted multiple emply values ...
    Can you please guide me, where i am doing wrong, i am using this code of urs :

    Please Login or Register  to view this content.
    Last edited by protocol; 08-17-2011 at 08:50 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1