VBA – Control Arrays


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2011/08/05/vba-control-arrays/

These are the few things that we will be covering in this post.

  • What is a Control Array?
  • Can we create Control Arrays in VBA?
  • Why do we need a Control Array?
  • Examples of Control Arrays in VBA
    a) Using existing textboxes in the UserForm and assigning a same set of event procedures
    b) Creating new textboxes at runtime and assigning them same set of event procedures

What is a Control Array?

A Control Array is a group of controls that share the same name type and the same event procedures. They are a convenient way to handle groups of controls (Same Type) that perform a similar function. All of the events available to the single control are available to the array of controls.

Can we create Control Arrays in VBA?

Even though, VBA doesn’t allow us to to create Control Array like in vb6 and vb.net, we can still create Control Array in VBA.

Why do we need Control Arrays in VBA?

Control Arrays mainly have these advantages

  1. Controls in a Control Array share the same set of event procedures. This results in you writing less amount of code.
  2. Control Arrays uses fewer resources.
  3. You can effectively create new controls at design time, if you need to.

Examples of Control Arrays in VBA

We would be covering the following here

a) Using existing textboxes in the UserForm and assigning a same set of event procedures
b) Creating new textboxes at runtime and assigning them same set of event procedures

Using existing textboxes in the UserForm and assigning a same set of event procedures

Let’s say you have 10 textboxes in your UserForm (see image below)

image

And you want all 10 to be numeric textboxes. Numeric textboxes are those text boxes where you can only type numbers. If it was just 1 TextBox, you would have a code like this

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
    '<~~ 48 to 57 is AscII code for numbers. 127 is for 'Delete' and 8 is for 'Backspace'
    Case 48 To 57, 127, 8
    Case Else
        KeyAscii = 0
    End Select
End Sub

Now imagine writing this code 10 times for each and every textbox?

This is where we will use Control Array of Textboxes and assign them the same procedure.

To start with, add a new Class. You can do that by right clicking on the “VBAProject~~>Insert~~>Class Module”. See the two images below.

image

image

Now paste this code in the code area of the Class1 Module

Public WithEvents TextBoxEvents As MSForms.TextBox

Private Sub TextBoxEvents_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
    '<~~ 48 to 57 is AscII code for numbers. 127 is for 'Delete' and 8 is for 'Backspace'
    Case 48 To 57, 127, 8
    Case Else
        KeyAscii = 0
    End Select
End Sub

image

and in the UserForm Initialize event, paste this code.

Option Explicit

Dim TextArray() As New Class1

Private Sub UserForm_Initialize()
    Dim i As Integer, TBCtl As Control

    For Each TBCtl In Me.Controls
        If TypeOf TBCtl Is MSForms.TextBox Then
            i = i + 1
            ReDim Preserve TextArray(1 To i)
            Set TextArray(i).TextBoxEvents = TBCtl
        End If
    Next TBCtl
    Set TBCtl = Nothing
End Sub

image

And you are done! Now when you run the UserForm, all the textboxes will now show the same behavior Smile

To test it, simply run your UserForm and try typing anything in the textboxes. You will notice that you will not be able to type anything other than numbers or pressing the ‘Delete’ and the ‘Backspace’ button. Similarly you can create other events for textboxes like change(), click() etc.

Creating new textboxes at runtime and assigning them same set of event procedures

Now let’s take another scenario. Instead of creating textboxes at design time and then assigning them same set of event procedures, what we want to do is to create these textboxes at run time and then assign them same set of event procedures.
Let’s say your UserForm now simply looks like this.

image

Create the Class module as I have shown above and then paste the code which I gave above for the Class module.

Paste this in the the Initialize event of the UserForm.

Option Explicit

Dim TextArray() As New Class1

Private Sub UserForm_Initialize()
    Dim ctlTBox As MSForms.TextBox
    Dim TBoxTop As Long, i As Long

    '~~> Decide on the .Top for the 1st TextBox
    TBoxTop = 30

    For i = 1 To 10
        Set ctlTBox = Me.Controls.Add("Forms.TextBox.1", "txtTest" & i)

        '~~> Define the TextBox .Top and the .Left property here
        ctlTBox.Top = TBoxTop: ctlTBox.Left = 50

        '~~> Increment the .Top for the next TextBox
        TBoxTop = TBoxTop + 20

        ReDim Preserve TextArray(1 To i)
        Set TextArray(i).TextBoxEvents = ctlTBox
    Next
End Sub

and you are done :)

To test it, simply run your UserForm. You will see that the TextBoxes automatically get created and you will not be able to type anything other than numbers or pressing the ‘Delete’ and the ‘Backspace’ button. This is how your UserForm will look.

image

Hope this helps Smile

About these ads

8 responses to “VBA – Control Arrays

  1. sam October 16, 2011 at 6:11 am

    Siddharth,

    When I reach the 10 th text box and say enter I want it to add new 11th text box. When I enter in the 11th an say enter it shound add a new 12th text box…and so on,,,
    Is this possible.

  2. Siddharth Rout October 16, 2011 at 12:56 pm

    Yes that is possible but then that will be true for the 1st textbox as well. What I mean is, when you press enter in the 1st textbox, the 2nd will be created and so on so forth. You will also have to take care of the form height else after few additions you will not be able to “see” the newly added text boxes.

  3. sam October 16, 2011 at 1:38 pm

    We want to create a Form with 10 Text boxes by default.
    But if the user presses enter in the 10th text box then the 11th get created

  4. Siddharth Rout October 16, 2011 at 1:40 pm

    Sam for that, you will have to use a different approach. The above approach will apply that behavior for all textboxes.

  5. Jeff Weir November 10, 2011 at 3:06 am

    That’s a great intro to Class Modules. THanks.

  6. Branko Capek May 11, 2012 at 7:43 pm

    Very helpful, thanks a lot!

  7. Pingback: repeterende foutcontrole - Pagina 3 - Worksheet.nl

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 31 other followers

%d bloggers like this: