Microsoft Office Tutorials and References
In Depth Information
FORMAT A USER FORM TEXT BOX AS CURRENCY OR A PERCENTAGE
Private Sub tbPrin_BeforeUpdate(ByVal Cancel As MSForms.
ReturnBoolean)
If Int(Me.tbPrin.Value) = tb.Prin.Value Then
Me.tbPrin.Value = Format(Me.tbPrin.Value, "$#,##0")
Else
Me.tbPrin.Value = Format(Me.tbPrin.Value, "$#,##0.00")
End If
End Sub
Part
3
To format a value as a percentage, you must handle the situation where the
user already typed in a percentage. You also have to deal with the possibility
that someone would enter 20% as 0.2 and someone else might enter 20. Use
this code:
Private Sub tbInt_BeforeUpdate(ByVal Cancel As MSForms.
ReturnBoolean)
‘ Handle if they entered a % sign already
If Not Right(Me.tbInt.Value, 1) = "%" Then
If Me.tbInt.Value >= 1 Then
Me.tbInt.Value = Format(Me.tbInt.Value / 100, "0.00%")
Else
Me.tbInt.Value = Format(Me.tbInt.Value, "0.00%")
End If
End If
End Sub
The text box derived from this method will look great. The downside is that when
you use those values, the percentage value is stored as text. Excel ignores the
currency character, but it does not ignore the percent symbol. You need to use
the following code to strip out the percent sign and divide the number by 100
before it can be used in a loan calculation:
Private Sub CommandButton1_Click()
MyPct = tbInt.Value
MyPct = Left(MyPct, Len(MyPct) - 1) / 100
PmtAns = Application.WorksheetFunction.Pmt _
(MyPct / 12, Me.tbMonths, -Me.tbPrin.Value)
Me.LabAns = "The monthly payment is " & Format(PmtAns,
"$#,##0.00")
End Sub
The resulting user form is shown in Figure 147.
 
Search JabSto ::




Custom Search