Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Format User Form Field

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Chicago, IL USA
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How can I format one of the user for input fields as a precentage?

    Thanks

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you are using VBA then you can use the FormatPercent Function.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Chicago, IL USA
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am using VBA, but am not sure where I can find this function or how to use it. Would you be able to provide me with the sample code?

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Chicago, IL USA
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Does anyone have sample code for how to format a user form field as a percentage?

    I tried:
    Private Sub TextBox3_Change()
    TextBox3 = Format(TextBox3, "0%")
    End Sub

    But the act of typing 1 causes the value to default to 100% as opposed to 1%.

    What am I doing wrong?

    Thanks

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Chicago, IL USA
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I figured out the FormatPercent Function but need to figure out how to make it so that typing in 1 yields 1% not 100%. Does anybody have any ideas how to do this?

    Thanks

    My code is:
    Private Sub TextBox3_Change()
    TextBox3 = FormatPercent(TextBox3)
    End Sub

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Divide textbox3 by 100, as in:

    FormatPercent(textbox3/100).

    Dave

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Chicago, IL USA
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I get a type mismatch error for

    TextBox3 = FormatPercent(TextBox3 / 100)

    Any idea why?

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can't divide until you change the string in your text box to a number. Try formatpercent(csng(textbox3)/100). Csng will change the value to a single. Now you have to be careful that the user puts something that can be changed to a number in there. If they don't there will be an error. You can use the isnumeric funtion for that in an if statement. Error trapping is also the hardest part of using text boxes.

    Also, usually you'd use text property of the textbox function to get the value (textbox3.text). I'm not sure if you're using this or not.

    Dave

Some videos you may like

User Tag List

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
  •