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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 03:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com