To loop through all controls on a UserForm in Excel VBA, use a For Each loop over the UserForm.Controls collection, checking each control’s TypeName or ControlType to filter by kind. For example, For Each ctl In Me.Controls iterates every control on the form, letting you read or modify properties like Value or Enabled in one pass.
Why Loop Through UserForm Controls?
UserForms often contain many text boxes, combo boxes, checkboxes, and option buttons. Writing separate code for each control is tedious and error-prone. A loop lets you apply the same operation—clearing inputs, validating data, disabling controls, or collecting values—to every control at once. This is especially useful in data-entry forms where you want a “Clear All” button or a validation routine that checks all fields before saving.
Understanding the Controls Collection
Every UserForm has a built-in Controls collection that holds all controls placed on it, including frames and multi-page tabs. The collection is zero-based and supports both For Each and indexed access. When you loop, you get each control as a generic Control object. To work with specific properties, you need to identify the control type.
Basic Loop Structure
The simplest loop looks like this:
Dim ctl As Control
For Each ctl In Me.Controls
' Do something with ctl
Next ctl
This works for any control. However, not all controls have the same properties. For instance, a Label has a Caption but no Value. Attempting to access ctl.Value on a label will cause a runtime error. Therefore, you must filter by type.
Filtering by Control Type
Use TypeName(ctl) to get the control’s class name (e.g., “TextBox”, “ComboBox”, “CheckBox”). Alternatively, use the ControlType property, which returns a numeric constant. The most reliable approach is to check TypeName because it works across all VBA hosts.
Example: Clear all text boxes and combo boxes:
Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
End If
Next ctl
For checkboxes and option buttons, you might set Value = False.
Handling Frames and Multi-Page Controls
Controls placed inside a Frame or on a MultiPage are not directly in Me.Controls—they are in the frame’s or page’s own Controls collection. To loop through all controls recursively, you need to descend into each container.
Example recursive subroutine:
Sub ClearAllControls(frm As Object)
Dim ctl As Control
For Each ctl In frm.Controls
If TypeName(ctl) = "Frame" Then
ClearAllControls ctl ' recurse into frame
ElseIf TypeName(ctl) = "MultiPage" Then
Dim pg As Page
For Each pg In ctl.Pages
ClearAllControls pg
Next pg
Else
' handle simple control
If TypeName(ctl) = "TextBox" Then ctl.Value = ""
End If
Next ctl
End Sub
Call it with ClearAllControls Me.
Common Problems and Solutions
- Error 438 (Object doesn’t support this property): You tried to access a property that the control doesn’t have. Always filter by type first.
- Controls inside frames not being processed: As noted, you must recurse into containers.
- Looping through all controls but skipping some: Some controls like
TabStriphave their own pages; use the same recursion logic. - Performance issues with many controls: Loops are fast, but if you’re updating properties that cause repaints, use
Application.ScreenUpdating = Falsebefore the loop and set it back toTrueafter. - Changing control names inside loop: Avoid renaming controls while iterating; it can cause unexpected behavior.
Comparing Different Loop Methods
Besides For Each, you can use an indexed loop: For i = 0 To Me.Controls.Count - 1. This is useful when you need to delete controls or when you want to process them in reverse order. However, For Each is generally safer and more readable. There’s no built-in “filter” method; you must manually check types.
Frequently Asked Questions
How do I loop through only text boxes in a UserForm?
Use TypeName(ctl) = "TextBox" inside the loop. For example, to set all text boxes to empty: If TypeName(ctl) = "TextBox" Then ctl.Value = "".
Can I loop through controls on a worksheet as well?
Yes, but the syntax differs. For worksheet controls (like ActiveX buttons), use Sheet1.OLEObjects or Sheet1.Controls (for form controls). The UserForm method is for UserForms only.
How do I get the value of a control in a loop?
After filtering by type, access the appropriate property. For TextBox and ComboBox, use .Value. For CheckBox and OptionButton, use .Value (True/False). For ListBox, use .ListIndex or .Value depending on the MultiSelect property.
Why does my loop miss controls inside a Frame?
Because they are not in the form’s top-level Controls collection. You need to iterate through the Frame’s Controls collection, as shown in the recursive example above.
Is there a way to loop through all controls without recursion?
No, because containers like Frames and MultiPages nest controls. You must either recurse or manually flatten the hierarchy. Some advanced techniques use a loop with a stack, but recursion is simplest.
Can I loop through controls in a UserForm from a standard module?
Yes, if you pass the UserForm object to a subroutine. For example: Sub ProcessForm(frm As UserForm) and then call ProcessForm Me from the form’s code. Inside, use For Each ctl In frm.Controls.
Safety and Best Practices
Always save a copy of your workbook before running a macro that modifies controls, because macros cannot be undone with Ctrl+Z. When looping, avoid modifying the collection (like adding or removing controls) during iteration, as it can cause runtime errors. If you need to delete controls, collect them in an array first, then delete after the loop. Also, be mindful of control names—use meaningful names to make your code easier to read.
Related
- Excel Sum Showing Zero? Fixes That Work
- Excel Spreadsheet Zoom Lock: How to Freeze Zoom Level
- Excel Staff Schedule Template: Free Options & How to Build One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel UserForm: How to Create, Design, and Use FormsTo create an Excel UserForm in Microsoft 365 for Windows, press Alt+F11 to open the Visual Basic Editor,…
- Excel VBA Loops: How to Repeat Tasks EfficientlyAn Excel VBA loop repeats a block of code until it has processed a set number of items…
- Writing VBA for Pivot Tables in Excel: A Practical GuideYes—you can write VBA to create, refresh, and configure Excel PivotTables, including their row, column, and value fields.…
- Excel VBA Copy and Paste Values OnlyTo copy a range in Excel VBA and paste only its values, use Copy followed by PasteSpecial Paste:=xlPasteValues.…
- Excel VBA Visible Worksheet Property: How to Set ItIn Excel VBA, a worksheet’s visibility is controlled by its Visible property. Set it to xlSheetVisible to show…
- Excel Macro Step Into: How to Debug VBA Line by LineIn Excel VBA, Step Into runs a macro one statement at a time and enters any procedure or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.