Loop through all textbox in a userform to format for currency

zpierucci

New Member
Joined
Sep 5, 2019
Messages
42
I have 15 textbox (textbox1, textbox2, etc..) in a UserForm. I would like them to have the ("$#,##0.00") currency format. Is there a way to loop through all of the TextBox controls for this?

I have tried several variations of the TextBox1 = Format(TextBox1.value, "$#,##0.00") variations with no success. Please help.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,660
Office Version
  1. 2019
Platform
  1. Windows
Hi,

try this in your code & see if helps

VBA Code:
Dim i As Integer
    For i = 1 To 15
        With Me.Controls("TextBox" & i)
            .Value = Format(Val(.Value), "$#,##0.00")
        End With
    Next i

Dave
 
Solution

zpierucci

New Member
Joined
Sep 5, 2019
Messages
42
Hi,

try this in your code & see if helps

VBA Code:
Dim i As Integer
    For i = 1 To 15
        With Me.Controls("TextBox" & i)
            .Value = Format(Val(.Value), "$#,##0.00")
        End With
    Next i

Dave
Works like a charm!!! Thank you for your help.
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,820
Office Version
  1. 365
Platform
  1. Windows
If your user has already entered the figures into all of the textboxes, then looping through each one should work.
However, I'm guessing that you wish the user to enter a value, and it to be automatically displayed in the correct format, in which case, you may be better running the format code after the textbox has been exited:
Nb test on a COPY of your work, first...

VBA Code:
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
 TextBox3.Value = Format(TextBox3.Value, "$#,##0.00")
End Sub
 

Forum statistics

Threads
1,136,734
Messages
5,677,447
Members
419,693
Latest member
divtjd

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
Top