Formatting number in a UserForm TextBox

  • Thread starter Thread starter John Pierce
  • Start date Start date
J

John Pierce

The code below is doing what I want in incrementing an
interest rate in a text box by an 1/8 of a point. I would
like it to always display to 3 decimal places, but it goes:
5.125, 5.25, 5.375, 5.5, dropping trailing zeros.
I know it needs a Format. Any help is appreciated.

Private Sub UserForm_Activate()
txtInterestRate.Value = "5.000"
etc.
 
Private Sub spnInterestRate_SpinUp()
txtInterestRate.Value = format(cdbl(txtInterestRate.Value) _
+ 0.125,"0.000")
End Sub
 
Private Sub spnInterestRate_SpinUp()
txtInterestRate.Value = format(cdbl(txtInterestRate.Value) _
+ 0.125,"0.000")
End Sub
-------------------------------------
So, no change in how the initialized value is formatted?
That surprized me. I would never in a zillion years have
been able to come up with this. Well, maybe, someday.
I have seen things like the CDbl but don't know how to
use them, and on this subject, the VBA Help is not helpful
at all. Thanks, Tom. (Do you ever sleep?)
 
cdbl is a conversion function. The value in the textbox is stored as a text
string. Your original code added the number 0.125 to the text string in the
textbox. Excel handles this by doing an implicit conversion to number
(double or single) and adding the 0.125. I just made the conversion
explicit. ( the plus operator is overloaded in that it can be used to do
concatenation - sometimes this causes problems in the situation you have
although apparently it wasn't for you).

for example, from the immediate window:
? "5.000" + "0.125"
5.0000.125
Not what you were doing, but to illustrate.

I don't see any reason to change the initilized value. It shows 3 decimal
places and is a string.
 
Back
Top