Adding controls to a VBA Collection

I am trying to add specific form controls to a VBA collection, so that
I can pass the entire collection to another function, and access the
individual controls later. I have tried several different syntax
variations. In each case, the receiving function produces the error
'Object Required' when I try to access any property of a control in
the collection. If I reference a collection item in the debugger
using its ordinal position, I see the value of the control. So, on
the surface at least, it appears that I am only adding the control's
'value' to the collection, not the control itself; or maybe my syntax
for retrieving the control's properties is at fault. Any help would
be greatly appreciated.

Dim colControls As Collection
Dim ctl As Control

Set colControls = New Collection

'I've tried this
Set ctl = Me.txtDosage
colControls.Add (ctl)

'And this
colControls.Add Me.controls("txtLabel")

'And this
colControls.Add Me.txtLabel


'The code to receive the collection look like this
Public Function fcnLogAudit(lngRecordType As enuRecordType,
lngRecordKey As Long, _
colControls As Collection) As Boolean

Dim ctl As Control

'Error occurs on this line. If I look at ctl(1) I can see the
control's
'value, but I can't access any of it's properties
For Each ctl In colControls

If ctl.Text <> ctl.OldValue Then...

Re: Adding controls to a VBA Collection

"Nunzio" wrote in message
news:c9290594-4ec1-4a25-ab8f-d1c6a3cfef90@n20g2000hsh.google groups.com...
>I am trying to add specific form controls to a VBA collection, so that
> I can pass the entire collection to another function, and access the
> individual controls later. I have tried several different syntax
> variations. In each case, the receiving function produces the error
> 'Object Required' when I try to access any property of a control in
> the collection. If I reference a collection item in the debugger
> using its ordinal position, I see the value of the control. So, on
> the surface at least, it appears that I am only adding the control's
> 'value' to the collection, not the control itself; or maybe my syntax
> for retrieving the control's properties is at fault. Any help would
> be greatly appreciated.
>
> Dim colControls As Collection
> Dim ctl As Control
>
> Set colControls = New Collection
>
> 'I've tried this
> Set ctl = Me.txtDosage
> colControls.Add (ctl)
>
> 'And this
> colControls.Add Me.controls("txtLabel")
>
> 'And this
> colControls.Add Me.txtLabel
>
>
> 'The code to receive the collection look like this
> Public Function fcnLogAudit(lngRecordType As enuRecordType,
> lngRecordKey As Long, _
> colControls As Collection) As Boolean
>
> Dim ctl As Control
>
> 'Error occurs on this line. If I look at ctl(1) I can see the
> control's
> 'value, but I can't access any of it's properties
> For Each ctl In colControls
>
> If ctl.Text <> ctl.OldValue Then...


Just use an array. Define an array of controls, as follows:

Dim arrControls(10) As Control

Replace 10 with whatever number, or leave blank for a dynamic array.

Then, in your function, define the parameter using a variant type, as
follows:

Public Function fcnLogAudit(lngRecordType As enuRecordType,
lngRecordKey As Long, arrControls As Variant) As Boolean

Use the UBound() function to get the upper bound of the array if you use a
dynamic array. And you're done!

Neil

Re: Adding controls to a VBA Collection

On Nov 29, 8:40 pm, "Neil" wrote:
> "Nunzio" wrote in message
>
> news:c9290594-4ec1-4a25-ab8f-d1c6a3cfef90@n20g2000hsh.google groups.com...
>
>
>
> >I am trying to add specific form controls to a VBA collection, so that
> > I can pass the entire collection to another function, and access the
> > individual controls later. I have tried several different syntax
> > variations. In each case, the receiving function produces the error
> > 'Object Required' when I try to access any property of a control in
> > the collection. If I reference a collection item in the debugger
> > using its ordinal position, I see the value of the control. So, on
> > the surface at least, it appears that I am only adding the control's
> > 'value' to the collection, not the control itself; or maybe my syntax
> > for retrieving the control's properties is at fault. Any help would
> > be greatly appreciated.
>
> > Dim colControls As Collection
> > Dim ctl As Control
>
> > Set colControls = New Collection
>
> > 'I've tried this
> > Set ctl = Me.txtDosage
> > colControls.Add (ctl)
>
> > 'And this
> > colControls.Add Me.controls("txtLabel")
>
> > 'And this
> > colControls.Add Me.txtLabel
>
> > 'The code to receive the collection look like this
> > Public Function fcnLogAudit(lngRecordType As enuRecordType,
> > lngRecordKey As Long, _
> > colControls As Collection) As Boolean
>
> > Dim ctl As Control
>
> > 'Error occurs on this line. If I look at ctl(1) I can see the
> > control's
> > 'value, but I can't access any of it's properties
> > For Each ctl In colControls
>
> > If ctl.Text <> ctl.OldValue Then...
>
> Just use an array. Define an array of controls, as follows:
>
> Dim arrControls(10) As Control
>
> Replace 10 with whatever number, or leave blank for a dynamic array.
>
> Then, in your function, define the parameter using a variant type, as
> follows:
>
> Public Function fcnLogAudit(lngRecordType As enuRecordType,
> lngRecordKey As Long, arrControls As Variant) As Boolean
>
> Use the UBound() function to get the upper bound of the array if you use a
> dynamic array. And you're done!
>
> Neil

Thanks Neil. That hadn't occurred to me.

I've been trying different approaches, and I've found that by using a
scripting dictionary object instead of a collection, the technique
that I've been using now works. I like your idea better though.

-Dean

Re: Adding controls to a VBA Collection

"Nunzio" wrote in message
news:f17c3711-735a-4b60-a0e3-ac80ed6e4148@s19g2000prg.google groups.com...
> On Nov 29, 8:40 pm, "Neil" wrote:
>> "Nunzio" wrote in message
>>
>> news:c9290594-4ec1-4a25-ab8f-d1c6a3cfef90@n20g2000hsh.google groups.com...
>>
>>
>>
>> >I am trying to add specific form controls to a VBA collection, so that
>> > I can pass the entire collection to another function, and access the
>> > individual controls later. I have tried several different syntax
>> > variations. In each case, the receiving function produces the error
>> > 'Object Required' when I try to access any property of a control in
>> > the collection. If I reference a collection item in the debugger
>> > using its ordinal position, I see the value of the control. So, on
>> > the surface at least, it appears that I am only adding the control's
>> > 'value' to the collection, not the control itself; or maybe my syntax
>> > for retrieving the control's properties is at fault. Any help would
>> > be greatly appreciated.
>>
>> > Dim colControls As Collection
>> > Dim ctl As Control
>>
>> > Set colControls = New Collection
>>
>> > 'I've tried this
>> > Set ctl = Me.txtDosage
>> > colControls.Add (ctl)
>>
>> > 'And this
>> > colControls.Add Me.controls("txtLabel")
>>
>> > 'And this
>> > colControls.Add Me.txtLabel
>>
>> > 'The code to receive the collection look like this
>> > Public Function fcnLogAudit(lngRecordType As enuRecordType,
>> > lngRecordKey As Long, _
>> > colControls As Collection) As Boolean
>>
>> > Dim ctl As Control
>>
>> > 'Error occurs on this line. If I look at ctl(1) I can see the
>> > control's
>> > 'value, but I can't access any of it's properties
>> > For Each ctl In colControls
>>
>> > If ctl.Text <> ctl.OldValue Then...
>>
>> Just use an array. Define an array of controls, as follows:
>>
>> Dim arrControls(10) As Control
>>
>> Replace 10 with whatever number, or leave blank for a dynamic array.
>>
>> Then, in your function, define the parameter using a variant type, as
>> follows:
>>
>> Public Function fcnLogAudit(lngRecordType As enuRecordType,
>> lngRecordKey As Long, arrControls As Variant) As Boolean
>>
>> Use the UBound() function to get the upper bound of the array if you use
>> a
>> dynamic array. And you're done!
>>
>> Neil
>
> Thanks Neil. That hadn't occurred to me.
>
> I've been trying different approaches, and I've found that by using a
> scripting dictionary object instead of a collection, the technique
> that I've been using now works. I like your idea better though.
>
> -Dean

Great! Glad it worked for you.

Neil