Create a collection of controls

[Guest]

I am using Office 2003 on Windows XP.

I have several controls on a spreadsheet (non-ActiveX controls) and I want
to add these objects to a collection. How do I do this? What I have so far
follows, but ofcourse generates an error:

Dim colObject As New Collection
Dim vItem As Variant
Dim oItems As Controls
Dim lX As Long

Set oItems = ActiveSheet.Controls
For Each vItem In oItems
lX = lX + 1
colObject.Add vItem, lX
Next

Could someone please correct my code or supply example code on how to do
this? Thanks much in advance.

 

[Chip Pearson]

Quartz,

Try the following code

Dim colObject As New Collection
Dim vItem As Variant
Dim oItems As OLEObjects
Dim lX As Long

Set oItems = ActiveSheet.OLEObjects
For Each vItem In oItems
lX = lX + 1
colObject.Add vItem, Format(lX, "0")
Next


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Thanks Chip!
Shouldn't I be able to obtain a count of items in the collection using:

MsgBox colObject.Count

In a test there are three controls in the sheet, but the count returns zero...

 

[Chip Pearson]

Quartz,

Debug.Print colObject.Count

returns the correct count for me. Step through your code to
ensure that the items are indeed added to the collection.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Chip,
The items are NOT being added. I am using the exact code, what am I doing
wrong? Do I need a library reference?

I very much appreciate your assistance...

 

[Chip Pearson]

I can't think of a reason that they are not being added. Do you
have an On Error Resume Next in your procedure. If you do,
comment it out so you can see the run time error (if any) that is
causing the error. The only thing I can think of is that you are
attempting to add an item to the collection and the key value
already exists in the collection.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Ah, I see, I added a few ActiveX controls to the sheet and it works fine.
Actually, I'm using Non-ActiveX controls... how would I do that?

 

[Guest]

Chip,
The objects in question work if I dim them as Shapes rather than OLEObject,
but then, I get the ActiveX objects as well. I only want the NON-activeX
items.

Sorry, I have to leave now. I will repost tomorrow if I can't crack it.

Thanks again for your help!!!