|
Fixed Decimal in Text Box
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").R ange("J18").Value -- DS |
Fixed Decimal in Text Box
Hi Donald, Try this ..... txtGain.Value = format(Workbooks("MyWorkSheet.xls").Sheets("MyHist ory").Range("J18").Value," $0.00") -- Cheers Nigel "Donald" wrote in message ... 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").R ange("J18").Value -- DS |
Fixed Decimal in Text Box
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 "Nigel" wrote in message ... Hi Donald, Try this ..... txtGain.Value = format(Workbooks("MyWorkSheet.xls").Sheets("MyHist ory").Range("J18").Value," $0.00") -- Cheers Nigel "Donald" wrote in message ... 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").R ange("J18").Value -- DS |
Fixed Decimal in Text Box
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 "Donald" wrote in message ... 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 "Nigel" wrote in message ... Hi Donald, Try this ..... txtGain.Value = format(Workbooks("MyWorkSheet.xls").Sheets("MyHist ory").Range("J18").Value," $0.00") -- Cheers Nigel "Donald" wrote in message ... 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").R ange("J18").Value -- DS |
Fixed Decimal in Text Box
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 -- Donald "Tom Ogilvy" wrote in message ... 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 "Donald" wrote in message ... 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 "Nigel" wrote in message ... Hi Donald, Try this ..... txtGain.Value = format(Workbooks("MyWorkSheet.xls").Sheets("MyHist ory").Range("J18").Value," $0.00") -- Cheers Nigel "Donald" wrote in message ... 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").R ange("J18").Value -- DS |
Fixed Decimal in Text Box
..Text is what you see in the cell (Nicely formatted, in your case)
..Value is the underlying value. Maybe you'll see it he 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 wrote: 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 -- Donald "Tom Ogilvy" wrote in message ... 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 "Donald" wrote in message ... 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 "Nigel" wrote in message ... Hi Donald, Try this ..... txtGain.Value = format(Workbooks("MyWorkSheet.xls").Sheets("MyHist ory").Range("J18").Value," $0.00") -- Cheers Nigel "Donald" wrote in message ... 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").R ange("J18").Value -- DS -- Dave Peterson |
Fixed Decimal in Text Box
Cool example, thanks. I understand now.
-- Donald "Dave Peterson" wrote in message ... .Text is what you see in the cell (Nicely formatted, in your case) .Value is the underlying value. Maybe you'll see it he 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 wrote: 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 -- Donald "Tom Ogilvy" wrote in message ... 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 "Donald" wrote in message ... 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 "Nigel" wrote in message ... Hi Donald, Try this ..... txtGain.Value = format(Workbooks("MyWorkSheet.xls").Sheets("MyHist ory").Range("J18").Value," $0.00") -- Cheers Nigel "Donald" wrote in message ... 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").R ange("J18").Value -- DS -- Dave Peterson |
| All times are GMT +1. The time now is 06:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com