MrExcel Publishing
Your One Stop for Excel Tips & Solutions

a comma in a textbox


Posted by Sandy on December 02, 2001 6:06 PM

Hi group,
Is it possible to set up my textboxes so as to when i
enter 1000 i get 1,000.
Thanks, Sandy


Posted by Bariloche on December 02, 2001 6:34 PM

Sandy,

Could you be more specific? When do you want the comma to appear? While the form is still displayed or when the data is entered on a spreadsheet?


thanks

Posted by Sandy on December 02, 2001 6:46 PM

Only when the form is diplayed
Thanks
Sandy

Posted by Bariloche on December 02, 2001 7:14 PM

Sandy,

Looks like your best choice is to use the BeforeUpdate event, like so:

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

If IsNumeric(TextBox1.Text) Then
TextBox1.Text = Format(TextBox1.Text, "#,###")
Else
MsgBox "Hey! Enter a number!"
TextBox1.Text = ""
End If

End Sub


Paste that into the code sheet for your text box and when the user tabs to the next box or control the text will update with a comma. Some additional code may be necessary, but that should get you started.

have fun

Posted by Sandy on December 02, 2001 7:29 PM

Thank You!, Bariloche(NT)

Looks like your best choice is to use the BeforeUpdate event, like so: Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) If IsNumeric(TextBox1.Text) Then

: Only when the form is diplayed : Thanks : Sandy