I have two lists of names. One is several hundred names long. The other is <
20. They are stored in separate worksheets.

I have a combo box that is populated with the large list of names. Then what
I want to do is remove any names from the combo box list if they are in the
second list.

It appears that while I can do
cmbobox1.AddItem Name
I cannot do
cmbobox1.RemoveItem Name

If I understand correctly, RemoveItem requires the row number to remove.

So, what is the best way to find the row number for the item that I want to
delete?


Thanks,

Dan Perkins

I was able to write a function that would loop through the large list and
return the index number of a name from the smaller list. RemoveItem worked
for that.

I can't think of any better methods, but if someone has an idea, I would
love to hear it.

Thanks,

Dan Perkins

"Dan Perkins" wrote:

I have two lists of names. One is several hundred names long. The other is <
20. They are stored in separate worksheets.

I have a combo box that is populated with the large list of names. Then what
I want to do is remove any names from the combo box list if they are in the
second list.

It appears that while I can do
cmbobox1.AddItem Name
I cannot do
cmbobox1.RemoveItem Name

If I understand correctly, RemoveItem requires the row number to remove.

So, what is the best way to find the row number for the item that I want to
delete?


Thanks,

Dan Perkins

Dan, I presumed your smaller list is in a range on the active
worksheet. So, I just put a list of things in h3:h7 that were among
the items I had already loaded into combobox1. This code seeks out
those items and removes them from combobox1. Not very pretty. I'd
like to see one of the large brains improve on it. You may already
have something along these lines, but I thought I'd pass it along. I
did reset the listindex to the first item in the combobox in case the
listindex item had been removed. James

Dim a As Integer, cell As Range
For Each cell In Range("h3:h7")
For a = Me.ComboBox1.ListCount - 1 To 0 Step -1
If Me.ComboBox1.List(a) = cell Then
Me.ComboBox1.RemoveItem a
End If
Next a
Next cell
Me.ComboBox1.ListIndex = 0

Dan Perkins wrote:
I was able to write a function that would loop through the large list and
return the index number of a name from the smaller list. RemoveItem worked
for that.

I can't think of any better methods, but if someone has an idea, I would
love to hear it.

Thanks,

Dan Perkins

"Dan Perkins" wrote:

I have two lists of names. One is several hundred names long. The other is <
20. They are stored in separate worksheets.

I have a combo box that is populated with the large list of names. Then what
I want to do is remove any names from the combo box list if they are in the
second list.

It appears that while I can do
cmbobox1.AddItem Name
I cannot do
cmbobox1.RemoveItem Name

If I understand correctly, RemoveItem requires the row number to remove.

So, what is the best way to find the row number for the item that I want to
delete?


Thanks,

Dan Perkins

hi,
I was checking for removing items from listbox and i found your code very
useful.Thanks for that,just wanna remove all the items from the listbox.is
this the only way?or there is some other practical way?
thanks again,you save some time for me:)

--
Regards,
Baha


"Zone" wrote:

Dan, I presumed your smaller list is in a range on the active
worksheet. So, I just put a list of things in h3:h7 that were among
the items I had already loaded into combobox1. This code seeks out
those items and removes them from combobox1. Not very pretty. I'd
like to see one of the large brains improve on it. You may already
have something along these lines, but I thought I'd pass it along. I
did reset the listindex to the first item in the combobox in case the
listindex item had been removed. James

Dim a As Integer, cell As Range
For Each cell In Range("h3:h7")
For a = Me.ComboBox1.ListCount - 1 To 0 Step -1
If Me.ComboBox1.List(a) = cell Then
Me.ComboBox1.RemoveItem a
End If
Next a
Next cell
Me.ComboBox1.ListIndex = 0

Dan Perkins wrote:
I was able to write a function that would loop through the large list and
return the index number of a name from the smaller list. RemoveItem worked
for that.

I can't think of any better methods, but if someone has an idea, I would
love to hear it.

Thanks,

Dan Perkins

"Dan Perkins" wrote:

I have two lists of names. One is several hundred names long. The other is <
20. They are stored in separate worksheets.

I have a combo box that is populated with the large list of names. Then what
I want to do is remove any names from the combo box list if they are in the
second list.

It appears that while I can do
cmbobox1.AddItem Name
I cannot do
cmbobox1.RemoveItem Name

If I understand correctly, RemoveItem requires the row number to remove.

So, what is the best way to find the row number for the item that I want to
delete?


Thanks,

Dan Perkins