number format

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
1,074
Office Version
  1. 2019
Platform
  1. Windows
I'm having a difficult time understanding why my textbox will not display the last digit.

Cell Value = $1.23932
Cell format = NumberFormat = "_($* #,##0.00000_);_($* (#,##0.00000);_($* ""-""?????_);_(@_)"

Textbox format = Format(Range("H" & ActiveCell.Row), "0.00000")

the number is displayed as 1.23930.


Why is the number the number 2 not showing up????
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Probably because of the currency cell formatting. Try:

Rich (BB code):
Textbox format = Format(Range("H" & ActiveCell.Row).Value2, "0.00000")
 
Upvote 0
Use Format(Range("H" & ActiveCell.Row).Value2, "0.00000")

VBA interprets the cell format as a Currency format. So, VBA rounds the cell to 4 decimal places, when "reading" from the cell into range.Value.

(VBA rounds to 2 decimal places when "writing" into the cell using range.Value, when the cell format is a Currency format.)
 
Upvote 0
Thank you both... that solved the issue. Now to educate myself on Value2 :)
 
Upvote 0
The VBA help for range.Value2 is a little misleading, to wit:

``The only difference between this property and the Value property is that the Value2 property doesn’t use the Currency and Date data types. You can return values formatted with these data types as floating-point numbers by using the Double data type``.

It is not that it does not "use" (sic) the Currency and Date types. And it is not that we can or must "use" type Double.

It is that with range.Value2, cells formatted as type Currency and Date and variables that are type Currency and Date are not treated differently than type Double.

I cannot find where VBA help explains the special treatment of range.Value.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top