Userform Textbox Formatting Question

bisel

Board Regular
Joined
Jan 4, 2010
Messages
153
Hello All,

I am trying to format the text in an userform. Rather than merely displaying the value in the textbox as a plain number, I want to apply formatting. For example, percent or currency.

For example, on my userform, the user can enter a value for "Other Income". I am able to format the textbox after the user enters a value using this code for the textbox ...

VBA Code:
Private Sub otherincome_textbox_exit(ByVal Cancel As MSForms.ReturnBoolean)

    otherincome_textbox.Text = Format(otherincome_textbox.Text, "$#,###0")

End Sub

But when the userform is first opened, the textbox displays the value in plain text. I have tried to insert this code into the UserForm_Activate event, but to no avail ...

VBA Code:
Private Sub UserForm_Activate()
    dim statements
    otherincome_textbox.Text = Format(otherincome_textbox.Text, "$#,###0")
    ....
    more stuff

End sub

Can anyone help with the method to format the same textbox when the userform is first opened?

Thanks,

Steve
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,951
TextBoxes do not have number formats. The "underlying value" vs "what user sees" that cells do, is not available to TextBoxes.
TextBoxes contain strings.
The problem with putting currency symbols in the text (as you do with the Format statements) is that it puts a non-numeric character in the TextBox which creates problems with downstream conversion of that string to a number.
It is a ton easier for downstream calculation to put a label with a "$" just to the left of the the TextBox.
 

bisel

Board Regular
Joined
Jan 4, 2010
Messages
153
TextBoxes do not have number formats. The "underlying value" vs "what user sees" that cells do, is not available to TextBoxes.
TextBoxes contain strings.
The problem with putting currency symbols in the text (as you do with the Format statements) is that it puts a non-numeric character in the TextBox which creates problems with downstream conversion of that string to a number.
It is a ton easier for downstream calculation to put a label with a "$" just to the left of the the TextBox.

Thank you, Mike. I believe that is good advice and am going to follow that.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,167
Messages
5,623,123
Members
415,956
Latest member
Footballtend

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