Results 1 to 8 of 8

Formatting text boxes in VBA

This is a discussion on Formatting text boxes in VBA within the Excel Questions forums, part of the Question Forums category; I am building a project that has a user form that uses a number of text box fields to record ...

  1. #1
    New Member
    Join Date
    Jul 2002
    Posts
    23

    Default

    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.

  2. #2
    Board Regular XL-Dennis's Avatar
    Join Date
    Jul 2002
    Location
    Östersund, Sweden
    Posts
    1,922

    Default

    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


  3. #3
    New Member
    Join Date
    Jul 2002
    Posts
    23

    Default

    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.

  4. #4
    Board Regular XL-Dennis's Avatar
    Join Date
    Jul 2002
    Location
    Östersund, Sweden
    Posts
    1,922

    Default

    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

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default

    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 ]

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,948

    Default

    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.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default

    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 ]

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default

    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 ]

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com