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.

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" <[email protected]> wrote in message
news:[email protected]...
>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.

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" <[email protected]> wrote in message
> news:[email protected]...
> >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.
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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,
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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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.
> >>
> >>
> >>
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in
>> >> message
>> >> news:[email protected]...
>> >> >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.
>> >>
>> >>
>> >>
>>
>>
>>

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> >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,
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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in
> >> >> message
> >> >> news:[email protected]...
> >> >> >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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>