Let's say you want to show or hide a group of form controls based on some criteria.
There are several reasons you may want to do this:
- Protect data from accidental editing
- Use application-level "security" to adjust the UI based on the user's role
- Show or hide fields based on the kind of data (e.g., customer vs. vendor)
- Obscure sensitive information from "over-the-shoulder" attacks
Whatever the reason, there will inevitably be times that you want to provide some sort of special handling for a group of form controls. Depending on what those controls have in common, there are several different approaches you might use to loop through them in VBA:
- Every control
- By form section
- By control type
- By control's Tag property
- By parsing the Tag property (supports multiple groups)
- By control's name
- By control's Name property
Each example on this page assumes that you are writing code in the form's "code-behind" module (e.g.,
Form_Form1). In this context, the
Me variable refers to the current form object.
The first example simply loops through the form's
Controls collection, which is a collection of every control on the form. I'm just printing the control's name to the immediate window for demonstration purposes. In a real application, you would replace the
Debug.Print Ctl.Name line with one or more lines of code that does something interesting with the control object, such as show/hide or enable/disable it.
Dim Ctl As Control For Each Ctl In Me.Controls Debug.Print Ctl.Name Next Ctl
By Form Section
Forms have three sections you need to be concerned with*:
- Form Header:
- Form Detail:
- Form Footer:
Each of these sections has its own
Controls collection that only includes the controls that have been placed within that section.
Dim Ctl As Control For Each Ctl In Me.Section(acHeader).Controls ' OR acDetail OR acFooter Debug.Print Ctl.Name Next Ctl
* Forms also have a page header and page footer, but you should not use them. They only appear when the form is printed or in print preview view, but your users should not be printing your forms. That's what reports are for.
By Control Type
You can use the ControlType property to treat all of your text boxes or all of your combo boxes, etc., the same way:
Dim Ctl As Control For Each Ctl In Me.Controls Select Case Ctl.ControlType Case acLabel: Debug.Print "Label : "; Ctl.Name Case acTextBox: Debug.Print "Textbox: "; Ctl.Name Case acComboBox: Debug.Print "Combo : "; Ctl.Name Case acListBox: Debug.Print "Listbox: "; Ctl.Name End Select Next Ctl
For a full list of ControlType enumerations, see here: AcControlType enumeration (Access).
By Control's Tag Property
The Tag property is a handy little control property that accepts any string up to 2,048 characters long. You can use this property to identify groups of controls that you want to treat the same way.
For example, the code below will output the name of every control on our form that has a Tag value of "
Dim Ctl As Control For Each Ctl In Me.Controls If Ctl.Tag = "MyGroup" Then Debug.Print Ctl.Name Next Ctl
The Tag property can be set from the "Other" tab of the control's Property Sheet (
[F4]) in Form Design View:
One of the biggest advantages to this approach is that you can [Ctrl]-select or click-drag-select multiple controls in Form Design View and set all of their Tag values at one time.
Unfortunately, the opposite is not possible. There is no easy way to select every control that has a certain Tag property. If you want to be able to easily see which controls have been assigned some special handling, you may want to consider selecting controls by name as shown in the final two approaches.
By Parsing the Control's Tag Property
What if you need to use a control's
Tag property for multiple purposes?
You can use my Parse() function to embed multiple name-value pairs inside each control's Tag property. For example, you could have controls with the following Tag values:
Dim Ctl As Control For Each Ctl In Me.Controls If Parse(Ctl.Tag, "MyGroup", vbBoolean) Then Debug.Print Ctl.Name Next Ctl
The above code would output:
By Control Name
One downside to using the Tag property to identify which controls to show or hide is that there is no way to see which controls have that Tag property and which ones don't. By using a Select Case statement, we can visually see the name of every control that is getting special handling in one place within the code.
Dim Ctl As Control For Each Ctl In Me.Controls Select Case Ctl.Name Case "MyTextBox", _ "MyOtherTextBox" Debug.Print Ctl.Name End Select Next Ctl
By Control's Name Property
There are a few problems with the previous approach, though:
- You can have a typo when entering the control's name in VBA
- The name of the control could get changed
The advantage of using a control's
Name property–instead of using a literal string–is that you can verify at compile-time that each of the controls you have special handling for actually exists. When using the control's name as a string, you lose the benefit of compile-time checking. With string names, typos and renamed controls become logic errors. When using the control's Name property, typos and renamed controls are compile errors.
Dim Ctl As Control For Each Ctl In Me.Controls Select Case Ctl.Name Case Me.MyTextBox.Name, _ Me.MyOtherTextBox.Name Debug.Print Ctl.Name End Select Next Ctl