Create a collection of controls
 

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.

Create a collection of controls
 

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



"quartz" wrote in message
...
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.

Create a collection of controls
 

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" wrote:

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



"quartz" wrote in message
...
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.

Create a collection of controls
 

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



"quartz" wrote in message
...
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" wrote:

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



"quartz" wrote in message
...
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.

Create a collection of controls
 

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" wrote:

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



"quartz" wrote in message
...
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" wrote:

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



"quartz" wrote in message
...
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.

Create a collection of controls
 

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


"quartz" wrote in message
...
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" wrote:

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



"quartz" wrote in message
...
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" wrote:

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



"quartz" wrote in
message
...
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.

Create a collection of controls
 

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?

"quartz" wrote:

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" wrote:

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



"quartz" wrote in message
...
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" wrote:

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



"quartz" wrote in message
...
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.

Create a collection of controls
 

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!!!

"Chip Pearson" wrote:

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


"quartz" wrote in message
...
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" wrote:

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



"quartz" wrote in message
...
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" wrote:

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



"quartz" wrote in
message
...
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.