On the lack of control arrays.

[Jan Nordgreen]

I am using Excel 97.

As fas as I understand VB in Excel does not have control arrays, no controls
have an index property.

On a form I have seven labels called lbl1 to lbl7. I would like to set their
captions to a(i) where a is an array and i runs from 1 to 7.

The code below does not work:

for i = 1 to 7
labelname = "lbl" & format(i)
&labelname.caption = a(i)
next

but is there a way to construct the name of the control as a string, and
then use it?

I would hate to have to say:
lbl1.caption = a(1)
lbl2.caption = a(2)
etc

Any help is greatly appreciated.

Sincerely,
Jan Nordgreen

 

[Jake Marx]

Hi Jan,

You can use the Controls collection to do this:

Controls("lbl" & CStr(i)).Caption = a(i)

 

[Tom Ogilvy]

&labelname . . . this ain't foxpro, clipper, or dbase <g>

for i = 1 to 7
labelname = "lbl" & i
Userform1.Controls(labelname).caption = a(i)
next

"i" will be coerced to a string without an additional function or use
cstr(i) if you prefer

 

[John Green]

Jan,

You can use the following:

Controls(labelname).Caption = a(i)