Remove Item (?) from ComboBox

Tepa

New Member
Joined
May 30, 2002
Messages
36
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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

XL-Dennis

Well-known Member
Joined
Jul 27, 2002
Messages
1,920
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
 
L

Legacy 98055

Guest
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
 

Forum statistics

Threads
1,147,848
Messages
5,743,525
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top