Thread: 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").Range("J18").Value

--
DS

Hi Donald,
Try this .....

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

--
Cheers
Nigel



"Donald" <donstock@cox.net> wrote in message
news:eSobDIfUFHA.2892@TK2MSFTNGP14.phx.gbl...
> 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
>
> --
> DS
>
>

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" <nigel-sw@suxnospampanet.com> wrote in message
news:e8XqWOgUFHA.2892@TK2MSFTNGP14.phx.gbl...
>
> Hi Donald,
> Try this .....
>
> txtGain.Value =
> format(Workbooks("MyWorkSheet.xls").Sheets("MyHistory").Range("J18").Value,"
> $0.00")
>
> --
> Cheers
> Nigel
>
>
>
> "Donald" <donstock@cox.net> wrote in message
> news:eSobDIfUFHA.2892@TK2MSFTNGP14.phx.gbl...
>> 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
>>
>> --
>> DS
>>
>>
>
>

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" <donstock@cox.net> wrote in message
news:epcklaiUFHA.3584@TK2MSFTNGP14.phx.gbl...
> 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" <nigel-sw@suxnospampanet.com> wrote in message
> news:e8XqWOgUFHA.2892@TK2MSFTNGP14.phx.gbl...
> >
> > Hi Donald,
> > Try this .....
> >
> > txtGain.Value =
> >
format(Workbooks("MyWorkSheet.xls").Sheets("MyHistory").Range("J18").Value,"
> > $0.00")
> >
> > --
> > Cheers
> > Nigel
> >
> >
> >
> > "Donald" <donstock@cox.net> wrote in message
> > news:eSobDIfUFHA.2892@TK2MSFTNGP14.phx.gbl...
> >> 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
> >>
> >> --
> >> DS
> >>
> >>
> >
> >
>
>

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" <twogilvy@msn.com> wrote in message
news:uZ8G92iUFHA.4056@TK2MSFTNGP15.phx.gbl...
> 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" <donstock@cox.net> wrote in message
> news:epcklaiUFHA.3584@TK2MSFTNGP14.phx.gbl...
>> 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" <nigel-sw@suxnospampanet.com> wrote in message
>> news:e8XqWOgUFHA.2892@TK2MSFTNGP14.phx.gbl...
>> >
>> > Hi Donald,
>> > Try this .....
>> >
>> > txtGain.Value =
>> >
> format(Workbooks("MyWorkSheet.xls").Sheets("MyHistory").Range("J18").Value,"
>> > $0.00")
>> >
>> > --
>> > Cheers
>> > Nigel
>> >
>> >
>> >
>> > "Donald" <donstock@cox.net> wrote in message
>> > news:eSobDIfUFHA.2892@TK2MSFTNGP14.phx.gbl...
>> >> 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
>> >>
>> >> --
>> >> DS
>> >>
>> >>
>> >
>> >
>>
>>
>
>

..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 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" <twogilvy@msn.com> wrote in message
> news:uZ8G92iUFHA.4056@TK2MSFTNGP15.phx.gbl...
> > 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" <donstock@cox.net> wrote in message
> > news:epcklaiUFHA.3584@TK2MSFTNGP14.phx.gbl...
> >> 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" <nigel-sw@suxnospampanet.com> wrote in message
> >> news:e8XqWOgUFHA.2892@TK2MSFTNGP14.phx.gbl...
> >> >
> >> > Hi Donald,
> >> > Try this .....
> >> >
> >> > txtGain.Value =
> >> >
> > format(Workbooks("MyWorkSheet.xls").Sheets("MyHistory").Range("J18").Value,"
> >> > $0.00")
> >> >
> >> > --
> >> > Cheers
> >> > Nigel
> >> >
> >> >
> >> >
> >> > "Donald" <donstock@cox.net> wrote in message
> >> > news:eSobDIfUFHA.2892@TK2MSFTNGP14.phx.gbl...
> >> >> 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
> >> >>
> >> >> --
> >> >> DS
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >

--

Dave Peterson

Cool example, thanks. I understand now.

--
Donald
"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
news:427CAD85.DBFC8531@netscapeXSPAM.com...
> .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 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" <twogilvy@msn.com> wrote in message
>> news:uZ8G92iUFHA.4056@TK2MSFTNGP15.phx.gbl...
>> > 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" <donstock@cox.net> wrote in message
>> > news:epcklaiUFHA.3584@TK2MSFTNGP14.phx.gbl...
>> >> 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" <nigel-sw@suxnospampanet.com> wrote in message
>> >> news:e8XqWOgUFHA.2892@TK2MSFTNGP14.phx.gbl...
>> >> >
>> >> > Hi Donald,
>> >> > Try this .....
>> >> >
>> >> > txtGain.Value =
>> >> >
>> > format(Workbooks("MyWorkSheet.xls").Sheets("MyHistory").Range("J18").Value,"
>> >> > $0.00")
>> >> >
>> >> > --
>> >> > Cheers
>> >> > Nigel
>> >> >
>> >> >
>> >> >
>> >> > "Donald" <donstock@cox.net> wrote in message
>> >> > news:eSobDIfUFHA.2892@TK2MSFTNGP14.phx.gbl...
>> >> >> 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
>> >> >>
>> >> >> --
>> >> >> DS
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>
> --
>
> Dave Peterson