Control Array in VBA

[aikidokid]

I may be missing something very obvious here, and I have been away from programming for a couple of years, but I can't seem to create a control array, either textboxes or labels etc.
When I copy and paste they come out as TextBox2 TextBox3 etc

Is there an obvious answer here?

(I expect so, but I can't find it )

[Hack]

VBA question moved to Office Development

Are you using Excel? Word? Access? Which VBA platform are you developing with?

[aikidokid]

I am using Excel, sorry about that. :-)

[jeroen79]

Is there an obvious answer here?

Sure: You can't.

You could get close to a control array but it would require a lot of repetitive code to do so.

Private Text(2) As TextBox 'an array so we can address the controls by index
 
Private Sub Init 'run this at the start of the program to fill the array
    Set Text(1) = Text1
    Set Text(2) = Text2
End Sub
 
Private Sub TextChange(Index As Long) 'the master change sub for a textbox, it is told the index of the textbox
    MsgBox Text(Index).Text
End Sub
 
Private Sub Text1_Change() 'catch the change event of a control
    TxtChange 1 'and send it to the master change sub, along with the controls index
End Sub
Private Sub Text2_Change()
    TxtChange 2
End Sub

[aikidokid]

Thanks - seems a bit stupit that VBA doesn't allow arrays though.

Ah well, not that much of a problem.
Thanks for the option you suggested.

[DKenny]

Have a look at my post here on creating an event handler in VBA. It will give you a good approxamation of a control array.