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).....
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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:
Upvote 0
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.
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top