label captions

[randy]

I need an array to get the values from a list of labels. How do I do this
using:
for i = 1 to 5
a(i) = label(i)
next

I've tried different ways to do this but I get error. What am I doing wrong?
Thanks Randy

 

[Rick Rothstein \(MVP - VB\)]

Where is you Label at... on the worksheet or on a UserForm? If on the
worksheet, which toolbar did you get it from... the Drawing toolbar or the
Visual Basic toolbar?

Rick

 

[Norman Jones]

Hi Randy,

You do not indicate either the type or
location of the labels.

If the labels are on a Userform, try
something like:

'=========>>
Private Sub CommandButton1_Click()
Dim i As Long
Dim arr(1 To 5)

For i = 1 To 5
arr(i) = Me.Controls("Label" & i).Caption
Next i

MsgBox arr(2)
End Sub
'<<=========

If the labels are on a worksheet and are
from the Control Toolbox, try:

'=========>>
Public Sub Tester()
Dim SH As Worksheet
Dim arr(1 To 5) As String
Dim i As Long
Dim oleObj As OLEObject

Set SH = ThisWorkbook.Sheets("Sheet1") '<<==== CHANGE

For Each oleObj In SH.OLEObjects
If TypeOf oleObj.Object Is MSForms.Label Then
i = i + 1
arr(i) = oleObj.Object.Caption
If i = 5 Then Exit For
End If
Next oleObj

MsgBox arr(2)

End Sub
'<<=========

If the labels are on a worksheet and are
Forms labels, try:

'=========>>
Public Sub Tester2()
Dim SH As Worksheet
Dim arr(1 To 5) As String
Dim myLabel As Label
Dim i As Long

Set SH = ThisWorkbook.Sheets("Sheet1") '<<==== CHANGE


For i = 1 To 5
arr(i) = SH.Labels(i).Caption
Next i

MsgBox arr(2)

End Sub
'<<=========

 

[randy]

Thanks I'll give that a try.

Hi Randy,

You do not indicate either the type or
location of the labels.

If the labels are on a Userform, try
something like:

'=========>>
Private Sub CommandButton1_Click()
Dim i As Long
Dim arr(1 To 5)

For i = 1 To 5
arr(i) = Me.Controls("Label" & i).Caption
Next i

MsgBox arr(2)
End Sub
'<<=========

If the labels are on a worksheet and are
from the Control Toolbox, try:

'=========>>
Public Sub Tester()
Dim SH As Worksheet
Dim arr(1 To 5) As String
Dim i As Long
Dim oleObj As OLEObject

Set SH = ThisWorkbook.Sheets("Sheet1") '<<==== CHANGE

For Each oleObj In SH.OLEObjects
If TypeOf oleObj.Object Is MSForms.Label Then
i = i + 1
arr(i) = oleObj.Object.Caption
If i = 5 Then Exit For
End If
Next oleObj

MsgBox arr(2)

End Sub
'<<=========

If the labels are on a worksheet and are
Forms labels, try:

'=========>>
Public Sub Tester2()
Dim SH As Worksheet
Dim arr(1 To 5) As String
Dim myLabel As Label
Dim i As Long

Set SH = ThisWorkbook.Sheets("Sheet1") '<<==== CHANGE


For i = 1 To 5
arr(i) = SH.Labels(i).Caption
Next i

MsgBox arr(2)

End Sub
'<<=========

 

[Norman Jones]

Hi Randy,

The procedures are each intended to
respond to a specific label type: they
are not interchangeable.

Where are your labels situated and,
if they are on a worksheet, where did
they come from?