Dynamically Adding Labels to a Userform

[John]

I'm trying to add a series of labels to a userform at runtime. I'm
stuck on a couple of aspects.

1. It looks like each label needs a unique name (true??). I'm not able
to do this.
2. When I set up each label, I need to assign it various properties.
Some of these are easy (e.g. height, width, etc.). But others don't
seem to be legal options (e.g. TextAlign)

Here is my non-working code:

'Generate Table of each problem attempted
Dim i As Integer
Dim newLbl As MSForms.Control
Dim lblName, strProb, strResult As String

For i = 1 To 1 'numAttempts

'Label for Question Number
lblName = "Forms.Label.Q" '& Right(i * 1000, 3) & ".1"
Set newLbl =
Me.Controls.Add(lblName) 'Run-time
error - Invalid Class String
newLbl.Caption = Range("results").Offset(i - 1, 0).Value

With newLbl
.Left = 54
.Top = 198
.Visible = True
.Height = 12
.Width = 24
.TextAlign = 'Not legal here??? How do I do this?
End With

Next i

Can anyone help? Thanks!

 

[Jim Cone]

It is easier to just add the label manually when you create the userform and make it invisible.
Then when you want to display it just change the visible property to True.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Data Options Excel add-in: row stuff/date picker/random stuff)





"John" <[email protected]>
wrote in message
news:[email protected]...

 

[John]

It is easier to just add the label manually when you create the userform and make it invisible.
Then when you want to display it just change the visible property to True..
--
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
(Data Options Excel add-in: row stuff/date picker/random stuff)

"John" <[email protected]>
wrote in message

Thanks, but I'm not making myself clear. These forms are added at
runtime. Prior to that, I have no idea how many of these I will be
adding. The idea is that I am creating a table on the userform that
pulls from worksheet values. There could be a single row in this table
or there could be 100 rows - I don't know. That's why I am adding them
dynamically and why I need to give each a unique name (also determined
at runtime).

Thanks for the reply.

 

[John]

It is easier to just add the label manually when you create the userform and make it invisible.
Then when you want to display it just change the visible property to True..
--
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
(Data Options Excel add-in: row stuff/date picker/random stuff)

"John" <[email protected]>
wrote in message

Thanks, but I don't think that I have explained this well enough. I am
adding an unknown number of labels - unknown until runtime, that is. I
am creating a table of 5 columns and an unknown number of rows. What I
showed above is just for the creation of the upper left-most label in
this table. There might wind up being just 1 row or there could be 100
rows - I have no way of knowing and it will vary on every execution.
Basically, I am just copying a table from a worksheet over to the
userform.

That's why I need a way to give each new label a unique name (I think
that's true - the argument doesn't seem to be optional).

Also, now that I have stated the overall intention of my code, if
there is a better way to bring the values in these cells onto a
userform, I'm open to suggestions. My tactic is to just create a label
for each cell in the table and then use the value in each cell as the
caption for the corresponding label.

Thanks again!

 

[John]

Thanks, but I'm not making myself clear. These forms are added at
runtime. Prior to that, I have no idea how many of these I will be
adding. The idea is that I am creating a table on the userform that
pulls from worksheet values. There could be a single row in this table
or there could be 100 rows - I don't know. That's why I am adding them
dynamically and why I need to give each a unique name (also determined
at runtime).

Thanks for the reply.

AAAAGH!

I just realized that I was completely misunderstanding the arguments
to the Set newLbl line. The first argument is not the label name,
which is how I was using it. For anyone who stumbles on this, that
line (and its declaration) should look something like this:

Dim newLbl As MSForms.Label
Set newLbl = Me.Controls.Add("forms.label.1", lblName, True)

After that, my other problem with setting properties was a non-issue.

Thanks.

p.s. sorry about the double post. First reply didn't show up for about
20 minutes.

 

[Ludo]

AAAAGH!

I just realized that I was completely misunderstanding the arguments
to the Set newLbl line. The first argument is not the label name,
which is how I was using it. For anyone who stumbles on this, that
line (and its declaration) should look something like this:

Dim newLbl As MSForms.Label
Set newLbl = Me.Controls.Add("forms.label.1", lblName, True)

After that, my other problem with setting properties was a non-issue.

Thanks.

p.s. sorry about the double post. First reply didn't show up for about
20 minutes.- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hi John,

As Jim wrote, it's easyer to set your number of controls at design
time, but if its of any help, take a look at John Walkenbach's site
at following link:
http://spreadsheetpage.com/index.php/site/tip/creating_a_userform_programmatically/

This creates a new form with 14 controls in run time.
As long as you don't need action (event code) for the on runtime
created control, it can work, but if you need to add event code it can
become tricky.
you can't debug the event code in debug mode, but need to be in run
time mode.
You'll notice it when you try it.

You will need to make changes to John Walkenbach's code according to
your needs.

success.

Ludo