Consulting

Results 1 to 11 of 11

Thread: Formatting Text Boxes in UserForms

  1. #1

    Formatting Text Boxes in UserForms

    How does one set the number format in Text Boxes (e.g., % with one decimal point; general number with comma separator without decimals?)

    Also can the % symbol be set as a trailing character in the box where the percentages will be entered?

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    Hi,

    So what kind of text boxes are you dealing with? Is it on a UserForm? From the Controls Toolbox, Forms Toolbox?

  3. #3
    They are text boxes on the UserForm itself, using the Controls Toolbox.

  4. #4
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    Not that I know of. You can add an additional line to your code though, when inserting that value ...

    [vba]Private Sub CommandButton2_Click()
    [A1].Value = TextBox1.Value / 100
    [A1].NumberFormat = "00.0%"
    Unload UserForm1
    End Sub[/vba]

    If they enter it as a percent already (e.g. 0.15 is 15%) then take out the " / 100" portion. That is only if they enter 15 as 15%.

  5. #5
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    Hi,

    Is this the sort of thing that you mean?[vba]Private Sub TextBox1_AfterUpdate()
    Me.TextBox1.Text = Format(Me.TextBox1.Text, "0.0%")
    End Sub

    Private Sub TextBox2_AfterUpdate()
    Me.TextBox2.Text = Format(Me.TextBox2.Text, "#,##0")
    End Sub[/vba]HTH

  6. #6
    more or less; the % formatting for TextBox 1 works but the comma separated number format for TextBox2 does not

  7. #7
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    The #,##0 format works, but you need a 4 digit number to get the comma. If you want leading zeros you can use this:

    [VBA]
    Private Sub TextBox2_AfterUpdate()

    Me.TextBox2.Text = Format(Me.TextBox2.Text, "0,000")

    End Sub
    [/VBA]

  8. #8
    Could there be a problem with the location of the statements in relation to the the other subs? The formats work fine when first entered in the UserForm but, when the wizard is restarted, the last entered numbers still appear in general number format (e.g., 0.15 and 10000)

  9. #9
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    You may need to reformat it after the last update. Can you post an attachment of what you are working with and maybe we can help further.

  10. #10

    Formatting text boxes in multipage UserForm

    Thanks Jacob. Here is the zipped file. If the purpose of this workbook seems obscure, it is because it is a dry run for inserting data from a multipage form to specific cells in the various worksheets.

  11. #11
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Try this line in the initialize sub when the macro starts:

    [VBA]Me.TextBox1.Value = Format(.Worksheets("sheet1").Range("b3").Value, "Percent")[/VBA]

Posting Permissions

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