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.

Thanks
mike637Asked:
Who is Participating?
 
Michael FowlerConnect With a Mentor Solutions ConsultantCommented:
You can use this code

Michael
Private Sub UserForm_Initialize()   
   Dim i As Integer
   Dim ctrl As Control
   
   i = 2
   For Each ctrl In Me.Controls
      If ctrl.Name = "Label" & i Then
         ctrl.Caption = ThisWorkbook.Sheets("Data").Range("A" & i + 17)
         i = i + 1
      End If
   Next
End Sub

Open in new window

0
 
dlmilleCommented:
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

etc.,

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


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

Dave
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mike637Author Commented:
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?
0
 
dlmilleCommented:
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: " & ctrl.name
      If ctrl.Name = "Label" & i Then
         ctrl.Caption = ThisWorkbook.Sheets("Data").Range("A" & i + 17)
         debug.print "found name: " & ctrl.name & " and it is a label " & i & " caption"
         i = i + 1
      End If
   Next
End Sub
0
 
mike637Author Commented:
Experts

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.
0
 
Michael FowlerSolutions ConsultantCommented:
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

Michael
test.xls
0
 
patrickabCommented:
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.

Patrick
0
 
bsardesonCommented:
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
        Next
    Next
End Sub

Open in new window

test.xls
0
 
bsardesonCommented:
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.
0
 
NorieVBA ExpertCommented:
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

0
 
dlmilleCommented:
imnorie - very clever!

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

Dave
0
 
mike637Author Commented:
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.
0
 
bsardesonCommented:
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.
0
 
NorieVBA ExpertCommented:
Dave

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.

bsardeson

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.
0
 
dlmilleCommented:
Missing a parenthesis is a syntax error :)

Cheers,

Dvae
0
 
NorieVBA ExpertCommented:
David

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.
0
 
bsardesonCommented:
imnorie

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.

mike367
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!
0
 
NorieVBA ExpertCommented:
bsardeson

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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.