Mass Userform Textbox formatting as number via AfterUpdate()

patsfan1

New Member
Joined
Jul 30, 2019
Messages
11
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()      <wbr>  If Textbox422 <>  ""Then textbox422 = FormatNumber(Textbox422, 0)
Private Sub TextBox423_AfterUpdate()      <wbr>  If Textbox423 <>  ""Then textbox423 = FormatNumber(Textbox423, 0)
Private Sub TextBox424_AfterUpdate()      <wbr>  If Textbox424 <>  ""Then textbox424 = FormatNumber(Textbox424, 0)
Private Sub TextBox425_AfterUpdate()      <wbr>  If Textbox425 <>  ""Then textbox425 = FormatNumber(Textbox425, 0)
Private Sub TextBox426_AfterUpdate()      <wbr>  If Textbox426 <>  ""Then textbox426 = FormatNumber(Textbox426, 0)
Private Sub TextBox427_AfterUpdate()      <wbr>  If Textbox427 <>  ""Then textbox427 = FormatNumber(Textbox427, 0)
Private Sub TextBox428_AfterUpdate()      <wbr>  If Textbox428 <>  ""Then textbox428 = FormatNumber(Textbox428, 0).....
 

Steve_

Board Regular
Joined
Apr 28, 2010
Messages
167
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()      <wbr>  If Textbox422 <>  ""Then textbox422 = FormatNumber(Textbox422, 0)
Private Sub TextBox423_AfterUpdate()      <wbr>  If Textbox423 <>  ""Then textbox423 = FormatNumber(Textbox423, 0)
Private Sub TextBox424_AfterUpdate()      <wbr>  If Textbox424 <>  ""Then textbox424 = FormatNumber(Textbox424, 0)
Private Sub TextBox425_AfterUpdate()      <wbr>  If Textbox425 <>  ""Then textbox425 = FormatNumber(Textbox425, 0)
Private Sub TextBox426_AfterUpdate()      <wbr>  If Textbox426 <>  ""Then textbox426 = FormatNumber(Textbox426, 0)
Private Sub TextBox427_AfterUpdate()      <wbr>  If Textbox427 <>  ""Then textbox427 = FormatNumber(Textbox427, 0)
Private Sub TextBox428_AfterUpdate()      <wbr>  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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,860
Office Version
365
Platform
Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,860
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,085,831
Messages
5,386,209
Members
401,984
Latest member
BettaJ

Some videos you may like

This Week's Hot Topics

Top