Formatting text boxes in VBA

[FabianSparkle]

I am building a project that has a user form that uses a number of text box fields to record information input by the operator. These fields record information such as dates, monetary values as well general text (ie: names).

What I need to do is have the fields automatically format the input according to the type of input. For example, if the operator inputs the number 2835 into the monetary field, I want the form to automatically format that number to display $2,835.00.

Can anybody help me? Thanking you in advance.

 

[XL-Dennis]

Hi Fabian,

Following procedures check if the entered value is an integer and if so formatt it to currency.

The currencyformat is subject to the regional settings in Windows so it will apply Your local regional settings:

<pre>
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = CheckValue(TextBox3)
If Cancel = False Then
With TextBox3
.Text = FormatCurrency((.Text) * 1, NumDigitsAfterDecimal:=0)
End With
End If
End Sub

Private Function CheckValue(TxtBox As MSForms.TextBox) As Boolean
Dim stValue As String
stValue = TxtBox.Text

If IsNumeric(stValue) Then
If stValue < 0 Then
MsgBox "The input must be greater then 0.", vbExclamation
TxtBox.Text = ""
CheckValue = True
ElseIf stValue - Int(stValue) = 0 Then
CheckValue = False
Else
MsgBox "The number must be an integer.", vbExclamation
TxtBox.Text = ""
CheckValue = True
End If
Else
MsgBox "The input must a value and an integer.", vbExclamation
TxtBox.Text = ""
CheckValue = True
End If
End Function
</pre>

Kind regards,
Dennis

 

[FabianSparkle]

Hello Dennis

Thanks so much for your assistance. Unfortunately I have a problem running the macro as VBA doesn't recognise the "FormatCurrency" command.

What can I do about it? Is there another piece of code I can use that does the same thing? I hope so.

Thanks again for your assistance Dennis.

 

[XL-Dennis]

Hi Fabian,

Sorry for late reply.

Following give You an alternative approach for formatting to currency:

<pre>
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox3
.Value = Format(CLng(TextBox3.Text), "Currency")
End With
End Sub
</pre>

Kind regards,
Dennis

 

[NateO]

Howdy, pmfji. Dennis' code works well unless you have decimals, at least in the US, so another alternative:<pre>
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox1 = Format(TextBox1, "$#,###,###,###.00")
End Sub</pre>

Edit: Again didn't mean to butt in. Did we want to force an integer?
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-11-21 18:27

 

[Juan Pablo González]

On 2002-11-21 18:23, NateO wrote:
Howdy, pmfji. Dennis' code works well unless you have decimals, at least in the US, so another alternative:

Nate, the "currency" format is specified by the Windows regional settings if I'm not mistaken.

 

[NateO]

Nate, the "currency" format is specified by the Windows regional settings if I'm not mistaken.

So it seems, and I appreciate the follow-up. So if I go into the control panel I can change the rounding issue? I'll have to test. Thanks.

I don't tend to do this locally... Why would windows?!
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-11-22 13:50

 

[NateO]

Actually, it's not the windows settings that's casuing the rounding, it's the evaluating of the number as a long (CLng) instead of a Double, etc.... The following will allow decimals:<pre>
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox1
.Value = Format(CDbl(TextBox1.Text), "Currency")
End With
End Sub</pre>

Nice Dennis!
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-11-22 13:58