Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
label captions
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
label captions
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 "randy" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
label captions
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 '<<========= --- Regards. Norman "randy" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
label captions
Thanks I'll give that a try.
"Norman Jones" wrote: 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 '<<========= --- Regards. Norman "randy" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
label captions
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? --- Regards. Norman "randy" wrote in message ... Thanks I'll give that a try. "Norman Jones" wrote: 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 '<<========= --- Regards. Norman "randy" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
X axis captions | Charts and Charting in Excel | |||
Formatting Text Boxes & Label Captions in MultiPages | Excel Programming | |||
Userform Captions | Excel Programming | |||
rename label captions | Excel Programming | |||
cell values as label captions | Excel Programming |