Textbox decimal places

Topic

New Reply

Hi all,

I have set the control source for a textbox in a form to a cell in which the formatting is a number with 2 digits after the decimal. When the contents of the cell is, say .70, the textbox displays .7, when the contents of the cell is say .65, the textbox displays .65. Is there any way to force the textbox to display two digits after the decimal? I did not see anything in the properties panel which might indicate the possibility of setting this.

Thanks,

Reply | Quote

Replies

You do this through Format/Cells/Number/Custom (or right click on the cell and select Format Cells). Once you’re there you should see a number of options. The ones you want look like #,##0.00 or maybe like #,##0.00;(#,##0.00) – there are a number of built in options, or you can define your own.

Brooke

Reply | Quote

Hi Mike,
I don’t know of any easy way of doing that unless your textbox is always bound to the same cell in which case you would use code to update the value in the cell from the userform rather than directly binding the textbox to the cell. If you use the controlsource property, then what you get is, as you’ve found, the stored value rather than the displayed one.

Reply | Quote

Hi all,

The custom format did not change the results in the textbox. I did, however, update the textbox contents in the form_activate event and used the textbox_change event to update the cell as Rory suggested.

Reply | Quote

If the text box is in a form, you can put code into the BeforeUpdate event for the text box, as follows:

TextBox1.Value = Format(txtTest1.Value, “0.00”)

When you type a number into the box and leave the box (either by Enter, Tab, Arrow, click on another control, etc.), the number will be reformatted to two decimal places. If you type something other than a number, nothing will change. If you wanted to get fancier, you could put more code, checking to see if the new entry IsNumeric or not, as follows:

With TextBox1
If IsNumeric(.Value) Then
.Value = Format(.Value, “0.00”)

Else
.SelStart = 0
.SelLength = Len(.Text)
Cancel = True
MsgBox “Please enter a number”

End If

End With

The Cancel = True causes the focus to remain on the text box. The SelStart and SelLength cause the text they typed to be selected, so the user can immediately type a new entry without having to manually erase the previous entry. The MsgBox, of course, tells them the reason the entry is not valid.

If the text box is an ActiveX control on a worksheet, I’m not sure how to do this, but I suspect there is a similar method. If it is a simple text box from the “Forms” toolbar, I don’t think it is possible, but I could be wrong.

Reply | Quote

Thanks Jim, that looks very interesting, I did not know how to torture a text box so much! Much obliged!

Reply | Quote