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.

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.

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.

I don't know. I think they are retrieved in the order they were created,
which, if the names were changed, may not be alphabetical by name.

I would try it and find out: take the code I showed, remove the line that sets
the caption, and instead display the Name. I would try this several times,
closing and reopening the workbook between runs, to be sure the order,
whatever it is, is constant.

If you have to check the name of each label within the loop, then figure out
which caption to apply, I don't think you haven't improved anything compared
to what you are doing now.

On Tue, 28 Sep 2004 22:11:06 -0700, "Derrick Vandekraats"
<derrickv@earthlink_replace_with_dot_net wrote:

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.

Thank you, I'll give that a try.

Regards,
Derrick.
"Myrna Larson" wrote in message
...
I don't know. I think they are retrieved in the order they were created,
which, if the names were changed, may not be alphabetical by name.

I would try it and find out: take the code I showed, remove the line that
sets
the caption, and instead display the Name. I would try this several times,
closing and reopening the workbook between runs, to be sure the order,
whatever it is, is constant.

If you have to check the name of each label within the loop, then figure
out
which caption to apply, I don't think you haven't improved anything
compared
to what you are doing now.

On Tue, 28 Sep 2004 22:11:06 -0700, "Derrick Vandekraats"
<derrickv@earthlink_replace_with_dot_net wrote:

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.

BTW, for a label created from the Control Tool Box, the type is 12 rather than
8.

On Wed, 29 Sep 2004 19:20:21 -0700, "Derrick Vandekraats"
<derrickv@earthlink_replace_with_dot_net wrote:

Thank you, I'll give that a try.

Regards,
Derrick.
"Myrna Larson" wrote in message
.. .
I don't know. I think they are retrieved in the order they were created,
which, if the names were changed, may not be alphabetical by name.

I would try it and find out: take the code I showed, remove the line that
sets
the caption, and instead display the Name. I would try this several times,
closing and reopening the workbook between runs, to be sure the order,
whatever it is, is constant.

If you have to check the name of each label within the loop, then figure
out
which caption to apply, I don't think you haven't improved anything
compared
to what you are doing now.

On Tue, 28 Sep 2004 22:11:06 -0700, "Derrick Vandekraats"
<derrickv@earthlink_replace_with_dot_net wrote:

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.