PDA

View Full Version : Formatting Text Boxes in UserForms



K. Georgiadis
09-15-2004, 01:15 PM
How does one set the number format in Text Boxes (e.g., % with one decimal point; general number with comma separator without decimals?)

Also can the % symbol be set as a trailing character in the box where the percentages will be entered?

Zack Barresse
09-15-2004, 01:58 PM
Hi,

So what kind of text boxes are you dealing with? Is it on a UserForm? From the Controls Toolbox, Forms Toolbox?

K. Georgiadis
09-15-2004, 02:02 PM
They are text boxes on the UserForm itself, using the Controls Toolbox.

Zack Barresse
09-15-2004, 02:19 PM
Not that I know of. You can add an additional line to your code though, when inserting that value ...

Private Sub CommandButton2_Click()
[A1].Value = TextBox1.Value / 100
[A1].NumberFormat = "00.0%"
Unload UserForm1
End Sub

If they enter it as a percent already (e.g. 0.15 is 15%) then take out the " / 100" portion. That is only if they enter 15 as 15%.

Richie(UK)
09-16-2004, 02:40 AM
Hi,

Is this the sort of thing that you mean?Private Sub TextBox1_AfterUpdate()
Me.TextBox1.Text = Format(Me.TextBox1.Text, "0.0%")
End Sub

Private Sub TextBox2_AfterUpdate()
Me.TextBox2.Text = Format(Me.TextBox2.Text, "#,##0")
End SubHTH

K. Georgiadis
09-16-2004, 04:06 AM
more or less; the % formatting for TextBox 1 works but the comma separated number format for TextBox2 does not

Jacob Hilderbrand
09-16-2004, 04:39 AM
The #,##0 format works, but you need a 4 digit number to get the comma. If you want leading zeros you can use this:


Private Sub TextBox2_AfterUpdate()

Me.TextBox2.Text = Format(Me.TextBox2.Text, "0,000")

End Sub

K. Georgiadis
09-16-2004, 07:23 AM
Could there be a problem with the location of the statements in relation to the the other subs? The formats work fine when first entered in the UserForm but, when the wizard is restarted, the last entered numbers still appear in general number format (e.g., 0.15 and 10000)

Jacob Hilderbrand
10-09-2004, 04:46 AM
You may need to reformat it after the last update. Can you post an attachment of what you are working with and maybe we can help further.

K. Georgiadis
10-09-2004, 10:21 AM
Thanks Jacob. Here is the zipped file. If the purpose of this workbook seems obscure, it is because it is a dry run for inserting data from a multipage form to specific cells in the various worksheets.

Jacob Hilderbrand
10-09-2004, 11:15 AM
Try this line in the initialize sub when the macro starts:

Me.TextBox1.Value = Format(.Worksheets("sheet1").Range("b3").Value, "Percent")