Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Create Control Arrays in VBA UserForms

VBA How To

Create Control Arrays in VBA UserForms

by  Bowers74  Posted    (Edited  )
Some VBA users have read a book or two on Visual Basic and some of them have wished that they could create control arrays in VBA too.

Unfortunately, you can't create control arrays in VBA like in Visual Basic but there is a workaround for certain functions:

The below will insert the two items to each ComboBox in the UserForm. You can do the same for the TextBoxes like this:
[color blue]
Code: 
Dim ctrl As Control
For Each ctrl In UserForm1.Controls
    If TypeName(ctrl) = "ComboBox" Then
        With ctrl
            .Clear
            .AddItem "Oil"
            .AddItem "Gas"
        End With
    End If
Next ctrl
[/color]


The below will Disable and Lock all of the TextBoxes in the UserForm.
[color blue]
Code: 
Dim ctrl As Control
For Each ctrl In UserForm1.Controls
    If TypeName(ctrl) = "TextBox" Then
        With ctrl
            .Enabled = False
            .Locked = True
        End With
    End If
Next ctrl
[/color]


If you don't want to "insert" ALL of the controls, you can use the Tag property.

The below will Enable and Unlock all of the TextBoxes in the UserForm that are "tagged" 2.
[color blue]
Code: 
Dim ctrl As Control
For Each ctrl In UserForm1.Controls
    If TypeName(ctrl) = "TextBox" And ctrl.Tag = "2" Then
        With ctrl
            .Enabled = False
            .Locked = True
        End With
    End If
Next ctrl
[/color]

There are plenty of other variations that can be done doing this. Experiment and have fun!

Good Luck!

Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top