Link to home
Start Free TrialLog in
Avatar of mike637
mike637Flag for United States of America

asked on

vba label caption

Hello Experts:

In my vba code, I want the UserForm_Initialize to load the Label caption for labels 2 - 21 from ThisWorkbook.Sheets("Data").Range("A19:A38").

Can you help me out with the right code to do this?  What I am trying is not working.

Avatar of Michael Fowler
Michael Fowler
Flag of Australia image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'm not sure there's a way to enumerate labels to get to the captions

You may have to do this:

UserForm1.label1.caption = range("A19").value
UserForm1.label2.caption = range("A20").value


thru the last caption.

Is this what you're looking for?

It takes the form of this:

Sub loadUserform()

    Load UserForm1
    UserForm1.Label1.Caption = Range("A19").Value
    UserForm2.Label2.Caption = Range("A20").Value
    'do this through the last caption
    UserForm1.Label20.Caption = Range("A38").Value
End Sub

Open in new window

Ah - Michael - I was looking for just that approach.  Thanks for finding it so I didn't have to!

Avatar of mike637


I used this code and it worked for labels 2 through 9.  But nothing populated for 10 through 21.

I double checked the labels and they are named correctly, i.e. label10, label11 etc. etc.

Is there something that I need to add?
change your code to this and run it and look in the debug window, it should print all the label captions and you can see if it doesn't find any you think should be there:

Private Sub UserForm_Initialize()  
   Dim i As Integer
   Dim ctrl As Control
   i = 2
   For Each ctrl In Me.Controls
      debug.print "found name: " &
      If ctrl.Name = "Label" & i Then
         ctrl.Caption = ThisWorkbook.Sheets("Data").Range("A" & i + 17)
         debug.print "found name: " & & " and it is a label " & i & " caption"
         i = i + 1
      End If
End Sub
Avatar of mike637



The code keeps [running] until i break it.  

It only populates the single digit label numbers then it ends there and stays in the running mode.

Very confused here.
Here is an example workbook that is working for me. Could you please post your workbook so I can have a look what is happening

If on the UserForm the first Label is Label01 and the last is let's say Label10 and there are say only 3 Labels present a loop that examines each Control will never get to Label10 if the index is incremented by i=i+1. Thus only the single digit Labels will be assigned a Caption. To avoid this happening you need to make sure that the Labels are in numerical order and that if the last Label is Label11 then need to be 11 Labels on the UserForm.

Avatar of bsardeson

I prefer using the Control Tag property and the For Each loop structure for performing this and similar activity.  Essentially, if the control tag property does not exist in the range of cell IDs or the cell value is "" then nothing is populated for that control.

I've included an input box to show the power of using the for each loops to change the captions as necessary to provide some insight to future use.

Private Sub Userform_Initialize()
    Dim oControl As Control
    Dim oRange As Range
    Dim oCell As Variant, oRetVal As Variant
    oRetVal = InputBox("Populated Cells are A19 thru A38" & vbCr _
        & "Please enter a valid Range (xx:xx) for control captions" & vbCr & vbCr _
        & "Example: A19:A38 or A22:A28 or A30:A38, etc.")
    If oRetVal = vbCancel Or oRetVal = "" Then Exit Sub
    Set oRange = ThisWorkbook.Sheets("Data").Range(oRetVal)
    For Each oControl In Me.Controls
        For Each oCell In oRange.Cells
            If oControl.Tag = oCell.Column & oCell.Row Then
                If oCell.Text <> "" Then oControl.Caption = oCell.Text
            End If
End Sub

Open in new window

My solution DOES NOT require the Form Controls to be an in specific order.  The TAG property identifies which cell value to capture.  You could just as easily change the tag of Label20 to be the same TAG to change the caption to read the same as Label3.

Further, the form does not require that all labels exist for each label to change the caption.  As long as the data in the Range is available, the Form can be mixed up infinitely so long as the captions desired for use appear in the TAG field for each control.
Avatar of Norie
If you have labels called Label1, Label2 etc then this should work.

If it doesn't and none of the other code posted doesn't there must be something else going on.

eg are the labels called Label..? are the labels being added dynamically?

By the way I don't see anyway code that loops the Controls collection would do so infinitely, unless you are adding new controls to the userform dynamically. Even then it would be unlikely.
Private UserForm_Initialize
Dim I As Long
Dim rngCaption As Range
Dim lblName As String
    Set rngCaption = Worksheets("Data").Range("A18"
    For I = 1 To 20
        lblName ="Label" & I ' change to lblName = "Label" & Format(I, "00") if the labels are named Label01 Label02, etc
        Me.Controls(lblName).Caption = rngCaption.Offset(I)
    Next I
End Sub

Open in new window

imnorie - very clever!

PS - syntax error in line 5.  Line 9 does the offset require two parameters?

Avatar of mike637


Thank you for you assistance.  It took a bit, but I just had to rebuild the form box and everything worked fine with your code.
Note: My example can be used with any type of control, label, textbox, etc.  Performing form labeling and text entries this way is extremely beneficial for populating both labels and data on forms, specifically when using a database rather than excel.

The accepted solution relies on hard-coding the label names and the code to find the proper cell, therefore if there are any changes, there are now 3 locations to make the necessary changes.  1) in-code, 2) on the control properties, 3) in the spreadsheet.

Using data driven loops requires code once, and update the controls and spreadsheet.

The syntax on that line has nothing to do with Offset - it's not even been used yet.:)

It's just a missing closing paranthesis.


What about controls that don't use/have the Caption property?

There is perhaps a better way to show/store/enter data in userforms - a control like a listbox or combobox.
Missing a parenthesis is a syntax error :)



It's actually a compile error caused by incorrect syntax.:)

I thought you were asking/saying the Offset was the problem.

To answer your question though, Offset takes 2 parameters both of which are optional and default to 0 if missing.

So you could write Offset(I, 0) to make things clearer.

Just got into the habit of ommitting the column bit I suppose, which has actually caused me problems.

Controls that don't have captions should have labels, which can be set with the same for ... next concept.  Furthermore, you are quite correct, there are better solution methods ... If it were me, I would populate all my control captions, labels, names, etc from a database rather than a listbox, combobox, or spreadsheet.

Depending on the depth, complexity and available resources a programmer has, there are an infinite set of possible solutions.  I just presented a solution that answered the task/question presented by the author and allowed the author to review for future growth of their programming.

ultimately, you the programmer will decide your course of action ... all we "experts" can do is offer personal suggestions, best practices and "magical" solutions.  Ideally, pick the one that best works for you, not us.  

Happy coding!

I agree that adding labels for say textboxes is a good idea but it's not always done and can add a level of unneeded complexity to things.

Also using the Tag property can have it's problems, if you accidentally enter the wrong information there you might end up with a duplicate, incorrect or blank caption.

There's nothing wrong with any of the approachs taken by people and they all should, in theory anyway, do what the user wants.

Some are perhaps more complex than others, some look at it from a different angle....

Your last point is quite right, though I would add we all post based on what information has been given to us and perhaps with some assumptions.
mi comentario es que la solucion es muy buena