Results 1 to 5 of 5

Thread: Mass Userform Textbox formatting as number via AfterUpdate()

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Mass Userform Textbox formatting as number via AfterUpdate()

    Hello,
    I have a userform with a bunch textboxes. I would like to format most of them as numbers with no decimals. The group of textboxes I would like formatted in this manner are consecutively numbered. I have the below code, which works, however, it is extremely cumbersome given the number of textboxes.

    I am searching for code to format this consecutive range of textboxes (422-481) as a number upon being updated. Have not been able to find anything that accomplishes this. Any help simplfying this would be greatly appreciated.


    Code:
    Private Sub TextBox422_AfterUpdate()        If Textbox422 <>  ""Then textbox422 = FormatNumber(Textbox422, 0)
    Private Sub TextBox423_AfterUpdate()        If Textbox423 <>  ""Then textbox423 = FormatNumber(Textbox423, 0)
    Private Sub TextBox424_AfterUpdate()        If Textbox424 <>  ""Then textbox424 = FormatNumber(Textbox424, 0)
    Private Sub TextBox425_AfterUpdate()        If Textbox425 <>  ""Then textbox425 = FormatNumber(Textbox425, 0)
    Private Sub TextBox426_AfterUpdate()        If Textbox426 <>  ""Then textbox426 = FormatNumber(Textbox426, 0)
    Private Sub TextBox427_AfterUpdate()        If Textbox427 <>  ""Then textbox427 = FormatNumber(Textbox427, 0)
    Private Sub TextBox428_AfterUpdate()        If Textbox428 <>  ""Then textbox428 = FormatNumber(Textbox428, 0).....

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Mass Userform Textbox formatting as number via AfterUpdate()

    Quote Originally Posted by patsfan1 View Post
    Hello,
    I have a userform with a bunch textboxes. I would like to format most of them as numbers with no decimals. The group of textboxes I would like formatted in this manner are consecutively numbered. I have the below code, which works, however, it is extremely cumbersome given the number of textboxes.

    I am searching for code to format this consecutive range of textboxes (422-481) as a number upon being updated. Have not been able to find anything that accomplishes this. Any help simplfying this would be greatly appreciated.


    Code:
    Private Sub TextBox422_AfterUpdate()        If Textbox422 <>  ""Then textbox422 = FormatNumber(Textbox422, 0)
    Private Sub TextBox423_AfterUpdate()        If Textbox423 <>  ""Then textbox423 = FormatNumber(Textbox423, 0)
    Private Sub TextBox424_AfterUpdate()        If Textbox424 <>  ""Then textbox424 = FormatNumber(Textbox424, 0)
    Private Sub TextBox425_AfterUpdate()        If Textbox425 <>  ""Then textbox425 = FormatNumber(Textbox425, 0)
    Private Sub TextBox426_AfterUpdate()        If Textbox426 <>  ""Then textbox426 = FormatNumber(Textbox426, 0)
    Private Sub TextBox427_AfterUpdate()        If Textbox427 <>  ""Then textbox427 = FormatNumber(Textbox427, 0)
    Private Sub TextBox428_AfterUpdate()        If Textbox428 <>  ""Then textbox428 = FormatNumber(Textbox428, 0).....

    This cant be done. A textbox contains "STRING" values.

    However, you can refer to the TEXT contained and convert it..

    For example. If we take textbox423 and you type the number 423..

    Code:
       textbox423.text = 423
       myVar = textbox423.text
    in this case, myVar will be equal to "423" (Yes, the quotation marks are part of it because it is a string value)

    BUT if you do this...

    Code:
       textbox423.text = 423
       myVar = cINT(textbox423.text)
    in this case, myVar will be equal to 423 (without quotation marks because it is now an integer value and not a string value. CINT converts your value to the "closest integer")


    I believe this is your solution.


    PS...go TB12!
    Last edited by Steve_; Aug 2nd, 2019 at 12:45 PM.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Mass Userform Textbox formatting as number via AfterUpdate()

    You don't need to check if if the textbox is empty, you can just use
    Code:
    Private Sub TextBox422_AfterUpdate()
    Textbox422 = FormatNumber(Textbox422, 0)
    End Sub
    Rather than creating a sub for each box, you could loop through the boxes and format them based on a command button.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    New Member
    Join Date
    Jul 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Mass Userform Textbox formatting as number via AfterUpdate()

    Thank you for the assistance!

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Mass Userform Textbox formatting as number via AfterUpdate()

    Glad we could help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

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
  •