Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Common format for Labels in Userform
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Common format for Labels in Userform
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Common format for Labels in Userform
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Common format for Labels in Userform
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 ----- DennisE wrote: ----- 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining two ranges based on common a common value | Excel Discussion (Misc queries) | |||
Common footer but not common margins please -(Page 1 of 2) etc | Excel Discussion (Misc queries) | |||
Is there a way to have fixed format in a userform? | Excel Discussion (Misc queries) | |||
format userform text box | Excel Discussion (Misc queries) | |||
Restrict date format for UserForm | Excel Discussion (Misc queries) |