Textbox to 2 Decimal Places

Pete81

New Member
Joined
Aug 27, 2015
Messages
26
Hi,

I currently have a system in place where, on a button click, a value I have in a cell to 2 decimal places is entered in to a textbox. However, if the value has a decimal of .#0 or .00, the textbox rounds and to 1 or no decimal places respectively. I need the textbox to show the value, no matter what, to 2 d.p. Does anyone have code for this?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi Pete,
I'm still relatively new to VBA myself, but I tried this code with a button on a sheet with a textbox and it worked for me, so hopefully it will work for you too!
Code:
Private Sub TextBox1_Change()
TextBox1.Value = Format(TextBox1.Value, "##.##")


End Sub

Let me know how you go mate!
 
Upvote 0
Hi Pete,
I'm still relatively new to VBA myself, but I tried this code with a button on a sheet with a textbox and it worked for me, so hopefully it will work for you too!
Code:
Private Sub TextBox1_Change()
TextBox1.Value = Format(TextBox1.Value, "##.##")


End Sub

Let me know how you go mate!


Hi mate,

It probably should work, but I can't get it to. Here's the initial code which enters the data from the cell to the text box. Excuse the lengthy textbox naming:

Sheets("Quotation").Shapes("Text_Quote_Tray_Price").TextFrame.Characters.Text = Sheets("Costing").Range("I85").Value

It works fine, but if I85= 1.50, the textbox will return 1.5. I need it to return to 2 d.p.

Does that change things, or should your code still work?
 
Upvote 0
Oh I figured it out! Change the last # after the decimal place to a 0. That worked for me.
I might be able to figure out how to incorporate that into your code if you could attach the whole sub. Sorry, I'm still only learning as well.
 
Upvote 0
Hi Pete,

Just change the .value to .text

Code:
Sheets("Quotation").Shapes("Text_Quote_Tray_Price").TextFrame.Characters.Text = Sheets("Costing").Range("I85").[COLOR=#ff0000]Text[/COLOR]

Hope this helps,
Cheers,
Alan.
 
Upvote 0
Hi Pete,

Just change the .value to .text

Code:
Sheets("Quotation").Shapes("Text_Quote_Tray_Price").TextFrame.Characters.Text = Sheets("Costing").Range("I85").[COLOR=#ff0000]Text[/COLOR]

Hope this helps,
Cheers,
Alan.


Yes it did Alan. Thank you very much. Enigmus, thank you as well. I'm sure the problem wasn't with your code, but with mine.
 
Upvote 0
No prob glad it worked :)

just for info.... Enigmus (I assume) was working on a UserForm TextBox, Pete is using a standard worksheet textbox shape, unfortunately the codes are completly different. Worksheet textboxes are a lot more difficult to work with :(
 
Upvote 0
No worries, as I said I'm learning too, so thank you Alan for bring real expertise to the table. I was actually using an ActiveX object on a sheet. I have never had much luck with the alternative to ActiveX.
 
Upvote 0
Haha no expertise here! :p I'm learning as well, just nice to be able to learn and help at the same time on this forum :)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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