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:

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

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:

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

     With TextBox3

         .Value = Format(CLng(TextBox3.Text), "Currency")

     End With

End Sub

Kind regards,
Dennis

[NateO]

Howdy, pmfji. Dennis' code works well unless you have decimals, at least in the US, so another alternative:

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

TextBox1 = Format(TextBox1, "$#,###,###,###.00")

End Sub

Edit: Again didn't mean to butt in. Did we want to force an integer?
_________________
Cheers, Nate Oliver

[ 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, Nate Oliver

[ 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:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

     With TextBox1

         .Value = Format(CDbl(TextBox1.Text), "Currency")

     End With

End Sub

Nice Dennis!
_________________
Cheers, Nate Oliver

[ This Message was edited by: NateO on 2002-11-22 13:58 ]