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?
 

Enigmus345

Board Regular
Joined
Sep 9, 2014
Messages
97
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!
 

Pete81

New Member
Joined
Aug 27, 2015
Messages
26
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?
 

Enigmus345

Board Regular
Joined
Sep 9, 2014
Messages
97
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.
 

Alan_P

Well-known Member
Joined
Jul 8, 2014
Messages
596
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.
 

Pete81

New Member
Joined
Aug 27, 2015
Messages
26
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.
 

Alan_P

Well-known Member
Joined
Jul 8, 2014
Messages
596
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 :(
 

Enigmus345

Board Regular
Joined
Sep 9, 2014
Messages
97
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.
 

Alan_P

Well-known Member
Joined
Jul 8, 2014
Messages
596
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 :)
 

Forum statistics

Threads
1,082,438
Messages
5,365,531
Members
400,837
Latest member
ELMST616

Some videos you may like

This Week's Hot Topics

Top