Solved

vba label caption

Posted on 2011-02-13
19
3,265 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:mike637
  • 5
  • 4
  • 4
  • +3
19 Comments
 
LVL 23

Accepted Solution

by:
Michael74 earned 500 total points
ID: 34885262
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
 
LVL 41

Expert Comment

by:dlmille
ID: 34885284
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
 
LVL 41

Expert Comment

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

Dave
0
 

Author Comment

by:mike637
ID: 34885437
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
 
LVL 41

Expert Comment

by:dlmille
ID: 34885453
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
 

Author Comment

by:mike637
ID: 34885528
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
 
LVL 23

Expert Comment

by:Michael74
ID: 34885670
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
 
LVL 45

Expert Comment

by:patrickab
ID: 34887685
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
 
LVL 1

Expert Comment

by:bsardeson
ID: 34888071
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Expert Comment

by:bsardeson
ID: 34888095
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
 
LVL 33

Expert Comment

by:Norie
ID: 34893262
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
 
LVL 41

Expert Comment

by:dlmille
ID: 34893341
imnorie - very clever!

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

Dave
0
 

Author Closing Comment

by:mike637
ID: 34893389
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
 
LVL 1

Expert Comment

by:bsardeson
ID: 34893739
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
 
LVL 33

Expert Comment

by:Norie
ID: 34902538
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
 
LVL 41

Expert Comment

by:dlmille
ID: 34902830
Missing a parenthesis is a syntax error :)

Cheers,

Dvae
0
 
LVL 33

Expert Comment

by:Norie
ID: 34902875
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
 
LVL 1

Expert Comment

by:bsardeson
ID: 34903489
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
 
LVL 33

Expert Comment

by:Norie
ID: 34908623
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now