Remove Item (?) from ComboBox

[Tepa]

Hi,

I have a loop which goes through every sheet. From every sheet it will get values from a range and adds them in to a ComboBox. Now my problem is that because it goes through every sheet there might be same values. Values are like 2002/Jan, 2002/Feb, 2002/Mar and so on.
For example. In sheet1 could be only values from 2002/Jan to 2002/Dec. In Sheet2 could be values from 2002/Jan to 2003/Dec. In sheet3 could be values from 2003/Jan to 2004/Dec.
Is there a way how macro can neglet those values which already locate in the ComboBox??

Any help appreciated.

 

[XL-Dennis]

Tepa,

As far as I understand You want to create a unique list of items in the Combobox.

one way is to create a collection of uniques items like the following:<PRE><FONT color=blue>Private<FONT color=blue>Sub</FONT></FONT>UserForm_Initialize()<FONT color=blue>Dim</FONT>List<FONT color=blue> As</FONT><FONT color=blue> New</FONT>Collection<FONT color=blue>Dim</FONT>rnArea<FONT color=blue> As</FONT> Range, rnCell<FONT color=blue> As</FONT> Range<FONT color=blue>Dim</FONT>vaValues<FONT color=blue> As</FONT><FONT color=blue> Variant</FONT><FONT color=blue>Set</FONT>rnArea = ActiveSheet.Range(Range("C1"), Range("C65536").End(xlUp))<FONT color=blue>On Error</FONT><FONT color=blue>Resume</FONT>Next<FONT color=blue>For</FONT>Each rnCell In rnArea

List.Add rnCell.Value, CStr(rnCell.Value)<FONT color=blue>Next</FONT>rnCell<FONT color=blue>On Error</FONT><FONT color=blue>GoTo</FONT> 0<FONT color=blue>For</FONT>Each vaValues In List

UserForm1.ListBox1.AddItem vaValues

Next</PRE>

HTH,
Dennis
________________
"Windows was not able to find any keyboard. Press F1-button to try again or F2-button for cancel."
This message was edited by XL-Dennis on 2002-08-29 00:13

 

[Legacy 98055]

Yes.
Store your values in an array and compare each element before adding each value in your loop. If it is a duplicate, then do not add it. Post your procedure if you need an example.
Tom