control array

[chriske911]

I don't think it is possible to really create an array of controls in
VBA like you do in VB but...
can I dynamically generate and place controls on a form when loading or
opening it?
this way I can still iterate thru these controls to get their values

thnx

 

[Terry Kreft]

Firstly a control array is different to an array of controls.

You can have an array of controls in both VB and VBA but you can't have a
control array in VBA.

You cannot dynamically create controls on a form when loading it. You need
to either:-
a) Place a sufficient number of controls to fulfil you needs and then
set their visible property appropriately
or
b) Open the form in design mode and add the controls you need before
re-opening the form for user input.

You can place, as in position/move, controls on a form when opening it.

 

[chriske911]

Terry Kreft explained :

Firstly a control array is different to an array of controls.

You can have an array of controls in both VB and VBA but you can't have a
control array in VBA.

You cannot dynamically create controls on a form when loading it. You need
to either:-
a) Place a sufficient number of controls to fulfil you needs and then
set their visible property appropriately
or
b) Open the form in design mode and add the controls you need before
re-opening the form for user input.

You can place, as in position/move, controls on a form when opening it.

Terry Kreft

thnx
since I am not native english, could you explain the diff between the
both?

grtz

 

[Douglas J. Steele]

You can, but why do you think you need to?

Be aware that there's a limit of 754 controls you can add to a form over its
lifetime. If you're going to be adding and removing controls, you'll likely
run into this limit. It's more common to position all the possible controls
you might need on the form, then control their visibility and move them as
necessary.

BTW, to simulate a control array, simply use a naming convention for your
controls. For instance, to simulate a 6 text box control array, name the
text boxes Text1, Text2, ... Text6, then use code like:

Dim ctlCurr As Control
Dim intLoop As Integer

For intLoop = 1 To 6
Set ctlCurr = Me.Controls("Text" & intLoop)
' ctlCurr now points to one of Text1, Text2,... Text6
' Do what you will...

Next intLoop

 

[Arvin Meyer [MVP]]

You can fake control arrays in VBA. If you name your command buttons like:

cmdWhatever0
cmdWhatever1
cmdWhatever2
....
Then you can do:

Dim i As Integer

For i = 0 to 9
Me("cmdWhatever" & i).Visible = True
Me("cmdWhatever" & i).Value = i + 1
Next i

 

[Terry Kreft]

Control array
---------------
As you presumably know, in VB, you can create a number of controls on a form
and give them the same name but with different index properties. This can
be one either at design time or run time (using the Load command).

This is a control array.

Array of controls
------------------
This is simply an array to which you assign controls as the members.

So you could do something like

Dim varCtls as Variant

set varCtls = Array

Redim varCtls(0 to 3)

With me
set varCtls(0) = .Textbox1
set varCtls(1) = .Textbox2
set varCtls(2) = .Textbox3
set varCtls(3) = .Textbox4
end with