Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / May 2008

Tip: Looking for answers? Try searching our database.

label captions

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
randy - 26 May 2008 19:39 GMT
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) - 26 May 2008 19:48 GMT
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

>I need an array to get the values from a list of labels.  How do I do this
> using:
[quoted text clipped - 5 lines]
> wrong?
> Thanks Randy
Norman Jones - 26 May 2008 20:57 GMT
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

>I need an array to get the values from a list of labels.  How do I do this
> using:
[quoted text clipped - 5 lines]
> wrong?
> Thanks Randy
randy - 27 May 2008 01:24 GMT
Thanks I'll give that a try.

> Hi Randy,
>
[quoted text clipped - 76 lines]
> > wrong?
> > Thanks Randy
Norman Jones - 27 May 2008 01:34 GMT
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

> Thanks I'll give that a try.
>
[quoted text clipped - 79 lines]
>> > wrong?
>> > Thanks Randy
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.