Format a TextBox on a Userform to 2 decimal places

berty2000

Board Regular
Joined
Mar 29, 2011
Messages
71
Hi Guys,

Could you be so kind as to provide some code that will enable me to display a text box on a userform rounded up to 1 decimal place.

Where am I going wrong....
it calculates the number entered in textbox1 and divides it by a value that changes in cell O26 but the answer is in about 8 or more decimel places.
Only need like 65.3 as an answer not 65.277756942

This is the code ive used.

Private Sub CommandButton1_Click()

TextBox2.Value = Val(TextBox1.Value) / Range("O26").Value

End Sub
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

berty2000

Board Regular
Joined
Mar 29, 2011
Messages
71
Excellent nearly there
the sum I enter is 6500 / 72 = 90.27777
Would like it to round up if possible and display 90.3

At the min when calculate with your code it reads 090

Is there a roundup code
 

berty2000

Board Regular
Joined
Mar 29, 2011
Messages
71
try this

Code:
Private Sub CommandButton1_Click()
 
TextBox2.Value = format(Val(TextBox1.Value) / Range("O26").Value,"0,00")
 
End Sub
Excellent nearly there
the sum I enter is 6500 / 72 = 90.27777
Would like it to round up if possible and display 90.3

At the min when calculate with your code it reads 090

Is there a roundup code
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

try

Code:
TextBox2.Value = Application.Round(Val(TextBox1.Value) / Range("o26"), 1)

HTH
 

PoggiPJ

Active Member
Joined
Mar 25, 2008
Messages
330
How could you modify that format statement so that the UserForm will always display properly regardless of the user's country settings? For example, if that same form were in the US, the comma would need to be a decimal?
 
Last edited:

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,181
Office Version
  1. 365
Platform
  1. Windows
Use concatenation for that character:
Code:
application.DecimalSeparator
 

PoggiPJ

Active Member
Joined
Mar 25, 2008
Messages
330
could you please put that into context? All users, worldwide, will use the same application. There will not be a separate app for each country, so I do not know how to format it prior to the UserForm display
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,181
Office Version
  1. 365
Platform
  1. Windows
Code:
Private Sub CommandButton1_Click()
  TextBox2.Value = Format(Round(TextBox1.Value / Range("O26").Value, 2), "0" & Application.DecimalSeparator & "00")
End Sub
 

Forum statistics

Threads
1,175,656
Messages
5,898,691
Members
434,723
Latest member
Epeople

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
Top