Adding controls to a VBA Collection

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nunzio - 29 Nov 2007 22:49 GMT

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

Reply to this Message

Neil - 30 Nov 2007 01:40 GMT

>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
[quoted text clipped - 36 lines]
>
> 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

Reply to this Message

Nunzio - 30 Nov 2007 01:49 GMT

> >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
[quoted text clipped - 53 lines]
>
> 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

Reply to this Message

Neil - 30 Nov 2007 03:41 GMT

>> >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
[quoted text clipped - 62 lines]
>
> -Dean

Great! Glad it worked for you.

Neil

Reply to this Message