Fixed Decimal in Text Box

[Donald]

The following code displays a cell value in my text box(txtGain), how do I
limit the text display to 2 decimals when the value in "J18" contains more
than two decimals?

txtGain.Value = "$" &
Workbooks("MyWorkSheet.xls").Sheets("MyHistory").Range("J18").Value

 

[Nigel]

Hi Donald,
Try this .....

txtGain.Value =
format(Workbooks("MyWorkSheet.xls").Sheets("MyHistory").Range("J18").Value,"
$0.00")

 

[Donald]

Thanks Nigel, EXACTLY what I was looking for. I new there was a way to do
that, but I've been away from coding too long.

DS

--
Donald

Hi Donald,
Try this .....

txtGain.Value =
format(Workbooks("MyWorkSheet.xls").Sheets("MyHistory").Range("J18").Value,"
$0.00")

 

[Tom Ogilvy]

If you have it formatted on the sheet the way you want to see it, then

txtGain.Value =Workbooks("MyWorkSheet.xls") _
.Sheets("MyHistory").Range("J18").Text

would also work.

--
Regards,
Tom Ogilvy

Thanks Nigel, EXACTLY what I was looking for. I new there was a way to do
that, but I've been away from coding too long.

DS

 

[Donald]

Hello again Tom,
Your solution works, but I don't really understand exactly why.

If I change your code to the cells "value" rather than "text", then the text
box displays the exact value without any formatting and carried to 4
decimals (which is how many decimals the cell's "value" has at the moment.
<893.8781> (See modified code below)

Note: the cell's format is "currency" with 2 decimals, and to show the $
sign.
txtGain displays 893.8781

txtGain.Value = Workbooks("MyWorkSheet.xls") _
.Sheets("MyHistory").Range("J18").Value

 

[Dave Peterson]

..Text is what you see in the cell (Nicely formatted, in your case)
..Value is the underlying value.

Maybe you'll see it here:
Option Explicit
Sub testme()
With ActiveSheet.Range("a1")
.Value = Date
.NumberFormat = "dddd mmmm dd, yyyy"
Debug.Print "Value: " & .Value
Debug.Print "Text: " & .Text
End With
End Sub

I got this back.

Value: 05/07/2005
Text: Saturday May 07, 2005

Same thing will happen with your cell formatted as currency.

 

[Donald]

Cool example, thanks. I understand now.