Change properties of multiple controls in VBA?

[MikeB]

In design mode, it i spossible to select multiple controls at the same
time and then to change a property of all the selected controls at the
same time.

Can I do the same programatically? Is there some way I can group some
controls and then set some properties of all those controls to the
same setting?

Thanks

 

[Danny Lesandrini]

Yes, and yes.

Yes you can lasso a bunch of controls and change their commonly held
properties all at once.

You can also do it from code. Below is what I use for my ADP project. It
uses the CurrentProject.AllForms collection and I can't remember if that
works in an MDB file. (I usually use the Forms Container there, but I don't
have that code handy for cut-and-paste.)

Basically, you need to open the form in design mode, find & reference the
controls and then set their properties. Below I'm finding all Combo Box
controls and setting their ListRows property to 16. (The default of 8 never
seems like enough to me.)

Down below I commented out code to read the form properties of MenuBar and
Toolbar. Form properties may also be set this way.



Public Function FooCtls()
On Error Resume Next

Dim frm As Form
Dim ctl As Access.Control
Dim fObj As AccessObject
Dim strName As String

For Each fObj In CurrentProject.AllForms
DoEvents
DoCmd.OpenForm fObj.Name, acDesign

Set frm = Forms(fObj.Name)
strName = frm.Name

frm.AllowDesignChanges = False

For Each ctl In frm.Controls
If ctl.ControlType = acComboBox Then
ctl.ListRows = 16
End If
Next

'If frm.MenuBar <> "" Or frm.Toolbar <> "" Then
' Debug.Print strName & ":" & frm.MenuBar & "/" & frm.Toolbar
'End If

DoCmd.Close acForm, fObj.Name, acSaveYes
Next
Debug.Print "finished"

End Function

 

[Dirk Goldgar]

In design mode, it i spossible to select multiple controls at the same
time and then to change a property of all the selected controls at the
same time.

Can I do the same programatically? Is there some way I can group some
controls and then set some properties of all those controls to the
same setting?

I do this sort of thing by using the controls' Tag property to identify
them. This is something I do at design time, and then at run time I loop
through all the controls and do something to all those whose Tag property
contains a particular string. For example:

Dim ctl As Access.Control

For Each ctl In Me.Controls
If ctl.Tag Like "*LockMe*" Then
ctl.Locked = True
End If
Next ctl

 

[David W. Fenton]

CurrentProject.AllForms collection and I can't remember if that
works in an MDB file.

It does (well, post Access 2000).

 

[David W. Fenton]

I do this sort of thing by using the controls' Tag property to
identify them. This is something I do at design time, and then at
run time I loop through all the controls and do something to all
those whose Tag property contains a particular string. For
example:

Dim ctl As Access.Control

For Each ctl In Me.Controls
If ctl.Tag Like "*LockMe*" Then
ctl.Locked = True
End If
Next ctl

If you're needing to change properties multiple times within a form
session, then I will tend to assign these to a custom collection, so
I can walk that limited collection instead of having to walk the
whole controls collection checking tags. This is handy for things
like locking/unlocking controls.

 

[Dirk Goldgar]

If you're needing to change properties multiple times within a form
session, then I will tend to assign these to a custom collection, so
I can walk that limited collection instead of having to walk the
whole controls collection checking tags. This is handy for things
like locking/unlocking controls.

That makes sense, though I've never done it that way. On the other hand,
for practical purposes the performance difference is likely to be
indetectable.

 

[MikeB]

If you're needing to change properties multiple times within a form
session, then I will tend to assign these to a custom collection, so
I can walk that limited collection instead of having to walk the
whole controls collection checking tags. This is handy for things
like locking/unlocking controls.

That's more or less what I'm intending to do. I was hoping for some
super-elegant construct where (and this is purely fabricated language)
I could do this:

For all controls in GrpControl
With
.Locked
.Enabled
endFor

 

[hor vannara]

In design mode, it i spossible to select multiple controls at the same
time and then to change a property of all the selected controls at the
same time.

Can I do the same programatically? Is there some way I can group some
controls and then set some properties of all those controls to the
same setting?

Thanks

 

[David W. Fenton]

That makes sense, though I've never done it that way. On the
other hand, for practical purposes the performance difference is
likely to be indetectable.

As a matter of fact, it's *very* noticeable -- walking the whole
controls collection repeatedly is substantially slower than walking
a limited custom collection. I wouldn't have taken the time to
implement it with a custom collection if there were not a
performance difference that the user would notice.

Well, I *might* have implemented it anyway, given that I hate doing
things that I know are inefficient! But I tested and there was a
huge difference -- there wasn't even any need to write code to loop
and time it, because it was obvious just comparing two
otherwise-identical copies of a form implementing the two methods. I
was surprised it made such a marked difference.

 

[David W. Fenton]

:

I was hoping for some
super-elegant construct where (and this is purely fabricated
language) I could do this:

For all controls in GrpControl
With
.Locked
.Enabled
endFor

You mean like this:

Public Sub EnableEdit(mcol As Collection, bolSwitch As Boolean)
On Error GoTo Err_EnableEdit
Dim varCtl As Variant
Dim ctl As Control

For Each varCtl In mcol
Set ctl = varCtl
If ctl.Visible = True Then ctl.Locked = Not bolSwitch
Next varCtl

Exit_EnableEdit:
Set ctl = Nothing
Exit Sub

Err_EnableEdit:
MsgBox err & vbCrLf & err.Description, vbCritical, _
"Error in mdlWB.EnableEdit()"
Resume Exit_EnableEdit
End Sub

 

[MikeB]

You mean like this:

Public Sub EnableEdit(mcol As Collection, bolSwitch As Boolean)
On Error GoTo Err_EnableEdit
  Dim varCtl As Variant
  Dim ctl As Control

  For Each varCtl In mcol
    Set ctl = varCtl
    If ctl.Visible = True Then ctl.Locked = Not bolSwitch
  Next varCtl

Exit_EnableEdit:
  Set ctl = Nothing
  Exit Sub

Err_EnableEdit:
  MsgBox err & vbCrLf & err.Description, vbCritical, _
    "Error in mdlWB.EnableEdit()"
  Resume Exit_EnableEdit
End Sub

I guess!

I'm going to have to spend a little time looking at it and
understanding it. I'm not that fluent with VBA.

 

[Dirk Goldgar]

As a matter of fact, it's *very* noticeable -- walking the whole
controls collection repeatedly is substantially slower than walking
a limited custom collection. I wouldn't have taken the time to
implement it with a custom collection if there were not a
performance difference that the user would notice.

Well, I *might* have implemented it anyway, given that I hate doing
things that I know are inefficient!

I know; me, too.

But I tested and there was a
huge difference -- there wasn't even any need to write code to loop
and time it, because it was obvious just comparing two
otherwise-identical copies of a form implementing the two methods. I
was surprised it made such a marked difference.

That surprises me, because on the occasions I've done it -- looped through
all the controls to change properties on tagged ones -- I didn't notice any
slowness at all. It seemed effectively instantaneous. But maybe that was
on a relatively simple form. When you did this, was the form very complex,
with lots of controls?

 

[David W. Fenton]

I know; me, too.


That surprises me, because on the occasions I've done it -- looped
through all the controls to change properties on tagged ones -- I
didn't notice any slowness at all. It seemed effectively
instantaneous. But maybe that was on a relatively simple form.
When you did this, was the form very complex, with lots of
controls?

Define "lots of controls" -- I don't recall exactly how many. It was
a wizard-style form with tabs, and several separate collections. So,
maybe a total of 50 data-bearing controls (in the case where I
discovered the performance difference, it was with unbound forms).

It was also in Access97 days, back when our computers weren't as
fast as they are now.

But it was a really huge and completely obvious difference -- the
custom collections were completely instantaneous (no delay at all),
while walking the controls collection repeatedly required a pause
that was perceivable to the user. It wasn't *terrible* (not like 3
seconds or something), but it was a definite difference.

Once I'd written the utility code to do it, it was very easy to drop
it into any app where I needed the same kind of functionality. And
it certainly does streamline the process of maintaining multiple
groups of controls with certain things in common.

 

[Dirk Goldgar]

It was
a wizard-style form with tabs, and several separate collections. So,
maybe a total of 50 data-bearing controls (in the case where I
discovered the performance difference, it was with unbound forms).

You're right, that doesn't sound unreasonably complex.

It was also in Access97 days, back when our computers weren't as
fast as they are now.

That could be a factor, I suppose. I don't recall how long ago was the
first time I used this technique, or what sort of hardware it would have
been on.

But it was a really huge and completely obvious difference -- the
custom collections were completely instantaneous (no delay at all),
while walking the controls collection repeatedly required a pause
that was perceivable to the user. It wasn't *terrible* (not like 3
seconds or something), but it was a definite difference.

Once I'd written the utility code to do it, it was very easy to drop
it into any app where I needed the same kind of functionality. And
it certainly does streamline the process of maintaining multiple
groups of controls with certain things in common.

I'll bear it in mind, the next time I have occasion to do something like
this.

 

[MikeB]

On Jan 27, 2:26 pm, "David W. Fenton" <[email protected]>
wrote:

David,
I hope you son't mind helping a programming novifde out a little.
I've been studying this code and have a few questions to improve my
understanding of it.

You mean like this:

Public Sub EnableEdit(mcol As Collection, bolSwitch As Boolean)
On Error GoTo Err_EnableEdit
  Dim varCtl As Variant
  Dim ctl As Control

Is it a programing requirement of VBA that the objects in a collection
(mcol in this instance) are variant? Is that why you cast them into
Control?

  For Each varCtl In mcol
    Set ctl = varCtl
    If ctl.Visible = True Then ctl.Locked = Not bolSwitch

This I'm not clear - from my understanding, this seems counter-
intuitive, which I'm sure you would not code it it the way I
understand it.

If you send "True" to the function, I understand that it sets the
property to "NOT Locked"?

  Next varCtl

Exit_EnableEdit:
  Set ctl = Nothing
  Exit Sub

Is this merely to destroy the ctl object when you're done with the
function?

Err_EnableEdit:
  MsgBox err & vbCrLf & err.Description, vbCritical, _
    "Error in mdlWB.EnableEdit()"
  Resume Exit_EnableEdit
End Sub

Thanks.

 

[David W. Fenton]

m:

On Jan 27, 2:26 pm, "David W. Fenton"


Is it a programing requirement of VBA that the objects in a
collection (mcol in this instance) are variant? Is that why you
cast them into Control?

Yes, so far as I'm aware. I can't recall at this point exactly where
I got that information, but I certainly wouldn't be using Set inside
the loop if it were unnecessary.

Well, crap. I just tested it, and this seems to work:

For Each ctl In mcol
If ctl.Visible = True Then ctl.Locked = Not bolSwitch
Next ctl

This may be a change between earlier versions of Access and later,
as this is code I developed originally in A97.

Well, just tested in the A97 version of the app I'd just tested, and
it works just fine.

I'm pretty sure that I got this from the ADH97, but in five minutes
spent perusing it, can't find anything.

This I'm not clear - from my understanding, this seems counter-
intuitive, which I'm sure you would not code it it the way I
understand it.

If you send "True" to the function, I understand that it sets the
property to "NOT Locked"?

The code is to lock or unlock controls for editing. The function is
called "EnableEdit", which means that when I pass EnableEdit True,
it's going to UNLOCK the controls, i.e., making them editable.

The test for .Visible is because the form this comes from has
different record types, and different controls are displayed in
different contexts.

Is this merely to destroy the ctl object when you're done with the
function?

Yes. That line is one of excessive caution. By design, VBA should
release the reference when the ctl variable goes out of scope (i.e.,
when the subroutine finishes running). But because VBA uses
reference counting to do this, it sometimes screws up and doesn't
release the reference. Since the loop refers to each control in
turn, at the end of the loop, there's an outstanding implicit
reference to the last control in the loop. Setting the variable to
Nothing insures that there's no possibility that this reference will
remain unreleased when the code finishes.

And that line of code is there not because I'm smart enough to
figure that out, but because I'm smart enough to listen to brilliant
Access programmers -- in this case, it was Michael Kaplan who
recommended this for all loops through object collections.

An aside: You may not be very experienced with VBA, but you ask
*very* good questions!

 

[MikeB]

An aside: You may not be very experienced with VBA, but you ask
*very* good questions!

Wow. Thanks. That is the nicest thing someone has said to me in quite
a while.

 

[David W. Fenton]

:

Wow. Thanks. That is the nicest thing someone has said to me in
quite a while.

Well, the two questions you asked were:

1. is it necessary to use a variant variable in the loop?

2. what is the reason for setting the control variable to Nothing?

Those are both extremely penetrating questions that show a complete
understanding of the code.

And the answer to the first question was that my code was doing
something that turned out to be completely unnecessary. So, your
"beginner's" question happened to uncover an unnecessary
inefficiency in my code. So, by asking your question, you helped
*me* improve my code.

I'm really impressed with your ability to comprehend the code, given
your stated novice status with VBA.