Common format for Labels in Userform

[edgargracias]

Gents,
I have a Userform with about 160 labels. the label
captions are linked to the cells in the worksheet. the
cells have numeric values in them.

although i can get the userform to display each label
individually in number format.

I would like to know if there was a easy way to do this,
instead of writing 160 lines to show the format i want.

below is a format i have for each label
Label1.Caption = Format(Label1.Caption, "#,##0.00")
this is the common format i have for all the labels

thanks.....Edgar

 

[DennisE]

Edgar,

I do virtually the same thing as you. The trick is to change the label names
from whatever they are to Label001 to Label160.
Suppose the respective label captions are contained in Sheet1 from Cell A1
through A160. In a VBA code module introduce

Sub MyLabels()
Dim ctl As Control, J As Variant
For each ctl in MyUserForm
If TypeName(ctl) = "Label" then
J = Right(ctl.Name, 3)
ctl.Caption = Sheets("Sheet1") _ .Range("A1").Offset(J - 1).Value
ctl.Caption = _
Format(ctl.Caption, "#,##0.00")
End If
Next ctl
End Sub

-- Dennis Eisen

 

[Guest]

Hi

I pasted below code and did the necessary changes as required, but i get an error as per belo

"For Each ctl In MyUserForm

dialog box says "Object Required

runtime Error 42

Pls advis

----- DennisE wrote: ----

Edgar

I do virtually the same thing as you. The trick is to change the label name
from whatever they are to Label001 to Label160
Suppose the respective label captions are contained in Sheet1 from Cell A
through A160. In a VBA code module introduc

Sub MyLabels(
Dim ctl As Control, J As Varian
For each ctl in MyUserFor
If TypeName(ctl) = "Label" the
J = Right(ctl.Name, 3
ctl.Caption = Sheets("Sheet1") _ .Range("A1").Offset(J - 1).Valu
ctl.Caption =
Format(ctl.Caption, "#,##0.00"
End I
Next ct
End Su

-- Dennis Eise

 

[Edgar]

Hi,

I did the changes as requested and got some results, but
not to my expections.

i also needed a thousand seperator and force a second
decimal(0.00) incase there is only one decimal
e.g.
1234.5 to display as 1,234.50

i am using excel 2003 SR1

pls help.

Edgar