Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 24th, 2004, 11:43 PM
Registered User
Join Date: Jul 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Arrays for Controls in VBA Excel

How can I make a group of controls on a userform, like Option Buttons, an array so that I can use them in a For/Next procedure?
Old July 25th, 2004, 05:39 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts

You cannot use control arrays in VBA. Try using the TAG property instead.
In this example there's 4 textboxes and a commandbtn:

Private Sub CommandButton1_Click()
Dim Ctl As Control
Dim i As Integer

For Each Ctl In UserForm1.Controls
    If Not Ctl.Tag = "" Then ' If omitted, all controls in form must have a TAG value
        For i = 1 To 4
            If Ctl.Tag = i Then
                Ctl.BackColor = vbYellow
            End If
    End If

End Sub

You decide which TAG value a control will have, so make series for your groups and loop in theese ranges.
Old July 25th, 2004, 10:42 AM
Authorized User
Join Date: Jun 2003
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts

True, VBA doesn't support control arrays, however you can simulate it quite well by creating a class that acts as a container for the control object.

At run-time you can dynamically create the VBA control (ie. MyForm.Controls.Add("MSForms.OptionButton") ), pass it the resulting control object in to your class, then add the class object to a collection.

You can then iterate using a For..Each loop quite nicely, and obviously group control functionality together.

If you do this, you'd almost certainly want to declare the control WithEvents in your class so that you can expose all the normal control events, such as Click and Change.

Hope that's what you meant, anyway!

Old October 3rd, 2010, 11:10 PM
Registered User
Join Date: Oct 2010
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Cool creating and using array of controls in VBA through class module

Hi gcianfanelli:
your idea is great. Could you please post a sample code for this.

I have several textboxes and several checkboxes in a form in VBA. Based on the user choice, the data has to be transferred to the SS. I have 24 controls and may increase. I like to create a class and use the built in methods of these controls. I really appreciate your code.

Similar Threads
Thread Thread Starter Forum Replies Last Post
vertical arrays in excel odcosar Excel VBA 1 October 13th, 2008 07:19 AM
Control Arrays in VBA? Sanchin Excel VBA 3 March 24th, 2008 04:20 PM
Code works in Excel VBA but not Access VBA fossx Access VBA 2 May 21st, 2007 08:00 AM
Matrix multiplication in VBA using arrays NewVBA Excel VBA 1 June 26th, 2006 03:15 PM
Excel VBA to SQL & back to VBA edesousa Excel VBA 1 June 1st, 2004 02:39 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.