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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,849
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,119,104
Messages
5,576,140
Members
412,700
Latest member
IIII
Top