Hello everyone,
Can you tell me how to add unique values in the Active X listbox.
This is my code :
But its giving me some error
Please Login or Register to view this content.
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.
Please Login or Register to view this content.
Never use 'additem' to populate a combobox/listbox:
If necessary adapt this code e.g. to A1:A4000Please Login or Register to view this content.
Thanks a lot...Originally Posted by Bob Phillips
Please Login or Register to view this content.
Is there only 2 peoples allowed to reply in here :-P
Thanks for the reply and sure i will surely follow your advice, i am new to excel programming so i really need such advicesNever use 'additem' to populate a combobox/listbox:
If necessary adapt this code e.g. to A1:A4000Please Login or Register to view this content.
Thanks a lot once again.
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?
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.
@Bob
Thanks for the reply
@Snb
Thanks for the code, i will try and post the result.Thanks a lot.
@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)
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.
@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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks