decimal places in currency

D

David Adamson

I just don't understand this.

When using the following 2 peices of code in a userform I cna format the
data to how I want it so as I'm typing in the data it displays in the
correct format I'm after.

Private Sub MaxQ_Change()
MaxQ = Format(MaxQ, "#,###")
End Sub

Private Sub MeanVC_Change()
MeanVC = Format(MeanVC, "$#,###")
End Sub

So in MaxQ_Change I type 10000, I get 10,000 appear as I'm typing

For in MeanVC_Change I type 15125, I get $15,125 appear.


But for the life of me I can not find a way to put decimals into the values,
where I want them

For example if I try
MeanVC= format(MeanVC, "$#,###.##) and I type 51, I get $5.1 and not $51.00
as I'm, after.

How can I type 51.65 into a textbox and get $51.65?

Thanks in advance
 
R

Robin Hammond

David,

I think you want zeroes after the decimal place in the format string. I also
added a missing quote at the end.

ie. MeanVC= format(MeanVC, "$#,###.00")

As a pointer, you might need to be a bit stricter on typing since there is
an implicit data conversion from the original text taking place. e.g. type
this in the immediate window for some unexpected results

? format("1/2", "$#,###.00")

Robin Hammond
www.enhanceddatasystems.com
 
D

David Adamson

Thanks for the posts Robin and Dave sorry for the delay but I was out of the
office

I tried

MeanVC= format(MeanVC, "$#,###.00")

And all that happens is as soon as you type 1 you get $1.00 and then the
next numbers are placed after the .00.

I think I will have to have a format button until I can find the answer
 
D

David Adamson

Had to change the _Change() at the end of the macro to exit, then it works

So

Private Sub MeanVC_Exit(ByVal Cancel As MSForms.ReturnBoolean)
MeanVC = Format(MeanVC, "$#,###.00")
End Sub
 
Top