Removing an Item From a Collection

[Dan Gesshel]

Hello.

I am attempting to remove an item from a collection and for some reason I'm
having trouble with the syntax. I can add items just fine:

Select Case Item
Case Is = "CD"
ActiveSheet.OLEObjects("Groups").Object.AddItem "CD Players"
Case Is = "DVD"
ActiveSheet.OLEObjects("Groups").Object.AddItem "DVD Players"
End Select

What do I need to remove a single item that I need to specifically identify?
(i.e. removing the "DVD" from the collection.) Any help would be greatly
appreciated.

Thanks.

Dan

 

[RADO]

Should be something like ...object.Items("CD Players").delete

 

[Dan Gesshel]

Hmm... trying different versions of this (sort of what I had the first time
in.) Still doesn't seem to be working. I'm obviously doing something wrong.

 

[RADO]

My guess is that you are not referring to the collection.
Collection in your case is OLEObjects, and the item is "Groups". You can
easily remove it by using

ActiveSheet.OLEObjects("Groups").Delete

but that's not what you want I think. As I understand, "DVD" is not a member
of any collection. If you are trying to populate a list, than the right
method is object.RemoveItem, but this has nothing to do with collections -
it's a method of a specific object you are accessing, like a listbox or a
combobox.

By the way, if you are trying to program a list box on a spreadsheet, there
are easier ways - let me know if you need help. Just describe your
objective.

RADO

 

[Tom Ogilvy]

It is remove, not delete.

There is sample code at John Walkenbach's site - it does a remove in the
sort portion of the code:

http://j-walk.com/ss/excel/tips/tip47.htm
Filling a ListBox With Unique Items

NoDupes is a collection:

For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i


From Help:
object.Remove index

index Required. An expression that specifies the position of a member of the
collection. If a numeric expression, index must be a number from 1 to the
value of the collection's Count property. If a string expression, index must
correspond to the key argument specified when the member referred to was
added to the collection.

So if you want to use a string like "DVD" when you add the item, you need to
make that the index.