UserForm Label & Format Problems

M

Mark Driscol

I'm trying to format some labels on a UserForm but am not getting results I
expect. The formats don't seem to act the same as if one were formatting a
cell in a worksheet. In fact, I had used these formats when formatting a
worksheet cell using a macro in a standard module and got the desired
results. Now that I am building a UserForm do to the same things, I get
different results. Any input into what I may be doing wrong would be
sincerely appreciated.


1. Me.ClosestLabel.Caption = "Closest: " & Format(MySum, "General")

will produce a label like "Closest: Ge43eral" if MySum, a declared Double
variable, has a value of 1843.78. If I try

Me.ClosestLabel.Caption = "Closest: " & Format(MySum, "0")

the label reads "Closest: 1843" for MySum with a value of 1843.78.


2. Me.IterationsLabel.Caption = "Iterations: " & Format(Counter,
"#,##0_);(#,##0);""-""_)")

produces labels like "Iterations: 145_)" for Counter, a declared Long
variable, being a positive number. This is different from how cells can be
formatted in that the "_)" is displayed instead of just leaving a space for
the right parenthesis.


3. Me.ElapsedTimeLabel.Caption = "Elapsed time: " & Format(Now -
StartTime, "[h]:mm:ss")

will show a value of ":12:02" after an elapsed time of two seconds.
StartTime is declared as a Double variable, and had previously been sent to
Now. I had expected it to show "0:00:02".


Mark
 
B

Bernie Deitrick

Mark,

The Format function uses different format strings than cell formatting.

1) If you want two decimals:
Me.ClosestLabel.Caption = "Closest: " & Format(MySum, "0.00")
If you want however many decimals the variable mySum has, don't use format
at all:
Me.ClosestLabel.Caption = "Closest: " & MySum

2) Not sure what you want to accomplish with this one....

3) Don't use the []'s and use the corrent form of the Now function:
Me.ElapsedTimeLabel.Caption = _
"Elapsed time: " & Format(Now() - StartTime, "h:mm:ss")

HTH,
Bernie
MS Excel MVP
 
T

Tom Ogilvy

2. Me.IterationsLabel.Caption = "Iterations: " & Format(Counter,"#,##0")

Possibly us "Time" instead of "NOW"

? time
11:25:17 AM
? now
4/14/2004 11:25:19 AM

--
Regards,
Tom Ogilvy



Bernie Deitrick said:
Mark,

The Format function uses different format strings than cell formatting.

1) If you want two decimals:
Me.ClosestLabel.Caption = "Closest: " & Format(MySum, "0.00")
If you want however many decimals the variable mySum has, don't use format
at all:
Me.ClosestLabel.Caption = "Closest: " & MySum

2) Not sure what you want to accomplish with this one....

3) Don't use the []'s and use the corrent form of the Now function:
Me.ElapsedTimeLabel.Caption = _
"Elapsed time: " & Format(Now() - StartTime, "h:mm:ss")

HTH,
Bernie
MS Excel MVP

Mark Driscol said:
I'm trying to format some labels on a UserForm but am not getting
Click to expand...
results
I
expect. The formats don't seem to act the same as if one were
Click to expand...
formatting
a
cell in a worksheet. In fact, I had used these formats when formatting a
worksheet cell using a macro in a standard module and got the desired
results. Now that I am building a UserForm do to the same things, I get
different results. Any input into what I may be doing wrong would be
sincerely appreciated.


1. Me.ClosestLabel.Caption = "Closest: " & Format(MySum, "General")

will produce a label like "Closest: Ge43eral" if MySum, a declared Double
variable, has a value of 1843.78. If I try

Me.ClosestLabel.Caption = "Closest: " & Format(MySum, "0")

the label reads "Closest: 1843" for MySum with a value of 1843.78.


2. Me.IterationsLabel.Caption = "Iterations: " & Format(Counter,
"#,##0_);(#,##0);""-""_)")

produces labels like "Iterations: 145_)" for Counter, a declared Long
variable, being a positive number. This is different from how cells can be
formatted in that the "_)" is displayed instead of just leaving a space for
the right parenthesis.


3. Me.ElapsedTimeLabel.Caption = "Elapsed time: " & Format(Now -
StartTime, "[h]:mm:ss")

will show a value of ":12:02" after an elapsed time of two seconds.
StartTime is declared as a Double variable, and had previously been sent to
Now. I had expected it to show "0:00:02".


Mark
Click to expand...
Click to expand...
 
M

Mark Driscol

Thanks, Bernie and Tom, your help is greatly appreciated.

* In your response to 1. below, I would like a general format, not having
to specify the number of decimal places in the string? Do you know how to
do this?

* Do you have anything that describes the differences in Format's strings
compared to how cells may be formatted? That would give guidance on my
questions 1. and 2.


Mark


Bernie Deitrick said:
Mark,

The Format function uses different format strings than cell formatting.

1) If you want two decimals:
Me.ClosestLabel.Caption = "Closest: " & Format(MySum, "0.00")
If you want however many decimals the variable mySum has, don't use format
at all:
Me.ClosestLabel.Caption = "Closest: " & MySum

2) Not sure what you want to accomplish with this one....

3) Don't use the []'s and use the corrent form of the Now function:
Me.ElapsedTimeLabel.Caption = _
"Elapsed time: " & Format(Now() - StartTime, "h:mm:ss")

HTH,
Bernie
MS Excel MVP

Mark Driscol said:
I'm trying to format some labels on a UserForm but am not getting
Click to expand...
results
I
expect. The formats don't seem to act the same as if one were
Click to expand...
formatting
a
cell in a worksheet. In fact, I had used these formats when formatting a
worksheet cell using a macro in a standard module and got the desired
results. Now that I am building a UserForm do to the same things, I get
different results. Any input into what I may be doing wrong would be
sincerely appreciated.


1. Me.ClosestLabel.Caption = "Closest: " & Format(MySum, "General")

will produce a label like "Closest: Ge43eral" if MySum, a declared Double
variable, has a value of 1843.78. If I try

Me.ClosestLabel.Caption = "Closest: " & Format(MySum, "0")

the label reads "Closest: 1843" for MySum with a value of 1843.78.


2. Me.IterationsLabel.Caption = "Iterations: " & Format(Counter,
"#,##0_);(#,##0);""-""_)")

produces labels like "Iterations: 145_)" for Counter, a declared Long
variable, being a positive number. This is different from how cells can be
formatted in that the "_)" is displayed instead of just leaving a space for
the right parenthesis.


3. Me.ElapsedTimeLabel.Caption = "Elapsed time: " & Format(Now -
StartTime, "[h]:mm:ss")

will show a value of ":12:02" after an elapsed time of two seconds.
StartTime is declared as a Double variable, and had previously been sent to
Now. I had expected it to show "0:00:02".


Mark
Click to expand...
Click to expand...
 
B

Bernie Deitrick

Mark,

If you want General, simply don't use the Format function - just pass the
variable as is.

I don't have any knowledge of the exact limitations of the Format function
format string.

HTH,
Bernie
MS Excel MVP

Mark Driscol said:
Thanks, Bernie and Tom, your help is greatly appreciated.

* In your response to 1. below, I would like a general format, not having
to specify the number of decimal places in the string? Do you know how to
do this?

* Do you have anything that describes the differences in Format's strings
compared to how cells may be formatted? That would give guidance on my
questions 1. and 2.


Mark


Bernie Deitrick said:
Mark,

The Format function uses different format strings than cell formatting.

1) If you want two decimals:
Me.ClosestLabel.Caption = "Closest: " & Format(MySum, "0.00")
If you want however many decimals the variable mySum has, don't use format
at all:
Me.ClosestLabel.Caption = "Closest: " & MySum

2) Not sure what you want to accomplish with this one....

3) Don't use the []'s and use the corrent form of the Now function:
Me.ElapsedTimeLabel.Caption = _
"Elapsed time: " & Format(Now() - StartTime, "h:mm:ss")

HTH,
Bernie
MS Excel MVP

Mark Driscol said:
I'm trying to format some labels on a UserForm but am not getting
Click to expand...
results
I
expect. The formats don't seem to act the same as if one were
Click to expand...
formatting
a
cell in a worksheet. In fact, I had used these formats when
Click to expand...
Click to expand...
formatting
a
worksheet cell using a macro in a standard module and got the desired
results. Now that I am building a UserForm do to the same things, I get
different results. Any input into what I may be doing wrong would be
sincerely appreciated.


1. Me.ClosestLabel.Caption = "Closest: " & Format(MySum, "General")

will produce a label like "Closest: Ge43eral" if MySum, a declared Double
variable, has a value of 1843.78. If I try

Me.ClosestLabel.Caption = "Closest: " & Format(MySum, "0")

the label reads "Closest: 1843" for MySum with a value of 1843.78.


2. Me.IterationsLabel.Caption = "Iterations: " & Format(Counter,
"#,##0_);(#,##0);""-""_)")

produces labels like "Iterations: 145_)" for Counter, a declared Long
variable, being a positive number. This is different from how cells
Click to expand...
can
be
formatted in that the "_)" is displayed instead of just leaving a
Click to expand...
space
for
the right parenthesis.


3. Me.ElapsedTimeLabel.Caption = "Elapsed time: " & Format(Now -
StartTime, "[h]:mm:ss")

will show a value of ":12:02" after an elapsed time of two seconds.
StartTime is declared as a Double variable, and had previously been
Click to expand...
sent
to
Now. I had expected it to show "0:00:02".


Mark
Click to expand...
Click to expand...
Click to expand...
 
M

Mark Driscol

Thank you, Bernie.

Mark


Bernie Deitrick said:
Mark,

If you want General, simply don't use the Format function - just pass the
variable as is.

I don't have any knowledge of the exact limitations of the Format function
format string.

HTH,
Bernie
MS Excel MVP

Mark Driscol said:
Thanks, Bernie and Tom, your help is greatly appreciated.

* In your response to 1. below, I would like a general format, not having
to specify the number of decimal places in the string? Do you know how to
do this?

* Do you have anything that describes the differences in Format's strings
compared to how cells may be formatted? That would give guidance on my
questions 1. and 2.


Mark


Bernie Deitrick said:
Mark,

The Format function uses different format strings than cell formatting.

1) If you want two decimals:
Me.ClosestLabel.Caption = "Closest: " & Format(MySum, "0.00")
If you want however many decimals the variable mySum has, don't use format
at all:
Me.ClosestLabel.Caption = "Closest: " & MySum

2) Not sure what you want to accomplish with this one....

3) Don't use the []'s and use the corrent form of the Now function:
Me.ElapsedTimeLabel.Caption = _
"Elapsed time: " & Format(Now() - StartTime, "h:mm:ss")

HTH,
Bernie
MS Excel MVP

I'm trying to format some labels on a UserForm but am not getting results
I
expect. The formats don't seem to act the same as if one were formatting
a
cell in a worksheet. In fact, I had used these formats when
Click to expand...
formatting
a
worksheet cell using a macro in a standard module and got the desired
results. Now that I am building a UserForm do to the same things, I get
different results. Any input into what I may be doing wrong would be
sincerely appreciated.


1. Me.ClosestLabel.Caption = "Closest: " & Format(MySum, "General")

will produce a label like "Closest: Ge43eral" if MySum, a declared Double
variable, has a value of 1843.78. If I try

Me.ClosestLabel.Caption = "Closest: " & Format(MySum, "0")

the label reads "Closest: 1843" for MySum with a value of 1843.78.


2. Me.IterationsLabel.Caption = "Iterations: " & Format(Counter,
"#,##0_);(#,##0);""-""_)")

produces labels like "Iterations: 145_)" for Counter, a declared Long
variable, being a positive number. This is different from how cells can
be
formatted in that the "_)" is displayed instead of just leaving a space
for
the right parenthesis.


3. Me.ElapsedTimeLabel.Caption = "Elapsed time: " & Format(Now -
StartTime, "[h]:mm:ss")

will show a value of ":12:02" after an elapsed time of two seconds.
StartTime is declared as a Double variable, and had previously been sent
to
Now. I had expected it to show "0:00:02".


Mark
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dynamically Adding Labels to a Userform 5
Currency Format for label in UserForm 3
TextBox Format/Editing Problem 1
Dynamically update userform label based on textbox 5
Retrieve Numeric Label for Max Value by Specific Month 8
Chart Date Label Problem - Is this a bug in Excel? 1
Time Calculation 10
How to Select Range based on dates in cells 1

Top