Thank you for the quick reply.

I see how this works, but in my original code, I knew specifically which
label I was setting the caption for. Will the first successful retrieval be
the txtX1 and the next one the txtX2 label or is there no way to tell of the
order of retrieval?

Regards,
Derrick.

"Myrna Larson" wrote in message
...
You need to loop through the Shapes collection, testing whether each shape
is
of a label (assuming there are other shapes besides these labels). I
assume
you have put the various captions into an array, Caps(). After adding some
labels from the old Forms toolbox, I found that their type is 8 =
msoFormControl

Dim Caps() As String
ReDim Caps(1 to 20)
'can you use a loop here to set the captions?

Counter = 0
For Each s In ActiveSheet.Shapes
If s.Type = msoFormControl Then 'type is 8
Counter = Counter + 1 'in case you have other shapes
s.Caption = Caps(Counter)
End If
Next s

But if you can't use a loop to load the array of captions, you need a
Select
Case block with 20 cases to do that. In which case, you're no farther
ahead
than with the 20 explicit statements.


On Tue, 28 Sep 2004 21:18:43 -0700, "Derrick Vandekraats"
<derrickv@earthlink_replace_with_dot_net wrote:

I am an occasional programmer and I would like some help on a problem:

I have 20 existing object labels on an Excel 2000 worksheet. They were
initially added with no attention to their caption property. I need these
labels to contain calculated data that periodically changes. My existing
code to update these labels has 20 lines like this:

txtX1.Caption = <some calculation
txtX2.Caption = <some calculation
.
.
.
txtX20.Caption = <some calculation

I want to use something more elegant like a for-next loop, but I can't
seem
to figure how to dynamically change the label object reference from txtX1
to
txtX2 to txtX3 (and so on) on each iteration of the for-next loop. If I
assemble a reference as a string variable and append the .Caption, it
generates a runtime error. What is the proper method for handling a task
like this? I have often had to handle problems like this using the above
method, but I'm sure there must be a better solution.

Regards,
Derrick.