I'm using VBA with Excel 5 and developing a form. Under VB6 I'd create a
control array to allow me to access 10 text boxes using a loop. VBA doesn't
appear to allow this. Is there any way to simulate a control array eg by
programatically modifying the names of the text boxes?

Thanks

I never worked on Excel 5, but I do use VBA Collection for loading the
different objects. You can load textboxes in a collection and reuse the
collection. However, I don't think VBA allows to change the names on runtime.
(Please correct me if I am wrong).


--
Thanks,
Vikas Bhandari
---------------------------
http://excelnoob.blogspot.com


"vb6user" wrote:

> I'm using VBA with Excel 5 and developing a form. Under VB6 I'd create a
> control array to allow me to access 10 text boxes using a loop. VBA doesn't
> appear to allow this. Is there any way to simulate a control array eg by
> programatically modifying the names of the text boxes?
>
> Thanks

VBA do not allow control arrays. Instead you can consider one of the below
two approaches


'------If the textboxes are named as TExtbox1,TExtbox2 ...Textbox10
Dim intCount as Integer
For intCount = 1 To 10
Me.Controls("Textbox" & intCount).Text = intCount
Next

'-------If you are not sure of the textbox names you can try the below
Dim Ctl As MSForms.Control
For Each Ctl In UserForm1.Controls
If TypeOf Ctl Is MSForms.TextBox Then
' do something
MsgBox Ctl.Name

End If
Next
--
Jacob


"vb6user" wrote:

> I'm using VBA with Excel 5 and developing a form. Under VB6 I'd create a
> control array to allow me to access 10 text boxes using a loop. VBA doesn't
> appear to allow this. Is there any way to simulate a control array eg by
> programatically modifying the names of the text boxes?
>
> Thanks

You can emulate a control array with an event class, but unfortunately not
all of the events are exposed to this class. Which events are you after?


---
HTH

Bob Phillips

"vb6user" <(E-Mail Removed)> wrote in message
news:8D546E0D-63B9-4F11-A971-(E-Mail Removed)...
> I'm using VBA with Excel 5 and developing a form. Under VB6 I'd create a
> control array to allow me to access 10 text boxes using a loop. VBA
> doesn't
> appear to allow this. Is there any way to simulate a control array eg by
> programatically modifying the names of the text boxes?
>
> Thanks