A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Setting the number of decimal places for a text box.



 
 
Thread Tools Display Modes
  #1  
Old March 7th 06, 04:55 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Setting the number of decimal places for a text box.


Hi All,

I have a user form and on that form is a text box. Data from a cell on
the worksheet is read into that text box and the user can observe it.

A simple calculation is done on the cell before it is read into the
text box. This produces a number with a large number of decimal places.
Even though I set the number of decimal places of that cell to 2 (via
right clicking on the cell and selecting "Format Cells...") the number
read into the text box still displays all the decimal places.

Could anyone tell me how I can set the number of decimal places of the
text box itself?

Best Regards,

Aaron


--
Aaron1978
------------------------------------------------------------------------
Aaron1978's Profile: http://www.excelforum.com/member.php...o&userid=31201
View this thread: http://www.excelforum.com/showthread...hreadid=519786

Ads
  #2  
Old March 7th 06, 05:31 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Setting the number of decimal places for a text box.


Try adding this to your formula:

=ROUND((your formula),2)

It will round your result to 2 decimal places.



"Aaron1978" wrote:

>
> Hi All,
>
> I have a user form and on that form is a text box. Data from a cell on
> the worksheet is read into that text box and the user can observe it.
>
> A simple calculation is done on the cell before it is read into the
> text box. This produces a number with a large number of decimal places.
> Even though I set the number of decimal places of that cell to 2 (via
> right clicking on the cell and selecting "Format Cells...") the number
> read into the text box still displays all the decimal places.
>
> Could anyone tell me how I can set the number of decimal places of the
> text box itself?
>
> Best Regards,
>
> Aaron
>
>
> --
> Aaron1978
> ------------------------------------------------------------------------
> Aaron1978's Profile: http://www.excelforum.com/member.php...o&userid=31201
> View this thread: http://www.excelforum.com/showthread...hreadid=519786
>
>

  #3  
Old March 8th 06, 08:32 AM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Setting the number of decimal places for a text box.


Thanks for your reply, but the calculation is done in VBA not in the
cell that the data is read from. Is there a way of rounding numbers in
VBA, I tried but can't seem to find a way.

Best Regards,

Aaron


--
Aaron1978
------------------------------------------------------------------------
Aaron1978's Profile: http://www.excelforum.com/member.php...o&userid=31201
View this thread: http://www.excelforum.com/showthread...hreadid=519786

  #4  
Old March 8th 06, 04:59 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Setting the number of decimal places for a text box.

Excel 2000 and later has a VBA Round function. It does ASTM rounding (rounds
to nearest rounded value, with ties [exactly 5] rounding up or down to make
an even number).

In earlier versions (or to always round 5 up) you can call the worksheet
Round function from VBA by either
Application.Round()
or
WorksheetFunction.Round()
The former former will work in all versions of Excel that support VBA. I
believe that the latter was introduced in Excel 97, but has the advantage of
prompting you with possible worksheet functions that can be called in this
way.

Jerry

"Aaron1978" wrote:

>
> Thanks for your reply, but the calculation is done in VBA not in the
> cell that the data is read from. Is there a way of rounding numbers in
> VBA, I tried but can't seem to find a way.
>
> Best Regards,
>
> Aaron
>
>
> --
> Aaron1978
> ------------------------------------------------------------------------
> Aaron1978's Profile: http://www.excelforum.com/member.php...o&userid=31201
> View this thread: http://www.excelforum.com/showthread...hreadid=519786
>
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Whole number converts to decimal dmbRedGetta Excel Discussion (Misc queries) 1 September 18th 05 06:58 PM
My numbers format as a number with two decimal places. Tim Poulter Excel Discussion (Misc queries) 2 September 18th 05 01:42 AM
How do I insert 2 decimal places to a number in cell? Rebecca Martinez Excel Worksheet Functions 6 July 7th 05 07:12 PM
Formulas dealing with text data Bagia Excel Worksheet Functions 6 June 20th 05 10:29 PM
How do I change the number of decimal places displayed in Excel Dan Excel Discussion (Misc queries) 1 February 23rd 05 10:31 PM


All times are GMT +1. The time now is 04:05 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.