I have a userform with many text boxes, all of which need to be formatted as
#,##0. I can write the code for each one seperately (I have started doing
this), but I thought there might be some coding I can do which will format
them all at once. There are so many text boxes on the form that doing this
seperately would be cumbersome.

Thanks in advance for any help.

Scott Settle

For Each ctl In .Controls
If TypeName(ctl) = "TextBox" Then
ctl.Text = Format(ctl.Text,"#,##0")
End If
Next ctl

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"umich1967" <u26373@uwe wrote in message news:660020f0bfef7@uwe...
I have a userform with many text boxes, all of which need to be formatted
as
#,##0. I can write the code for each one seperately (I have started doing
this), but I thought there might be some coding I can do which will format
them all at once. There are so many text boxes on the form that doing
this
seperately would be cumbersome.

Thanks in advance for any help.

Scott Settle

Scott, How are you setting the formatting? Are you use a TextBox Event?
If so please paste the code.

Charles

umich1967 wrote:
I have a userform with many text boxes, all of which need to be formatted as
#,##0. I can write the code for each one seperately (I have started doing
this), but I thought there might be some coding I can do which will format
them all at once. There are so many text boxes on the form that doing this
seperately would be cumbersome.

Thanks in advance for any help.

Scott Settle

Hi Scott,

I guess you wanna use textbox as a calculator texbox...
Try this one :

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
'On Error Resume Next
If IsNumeric(TextBox1.Value) Then
TextBox1.Value = FormatNumber(TextBox1.Value, 0)
'depends on you for number of digit after decimal
End If
End Sub

HTH,

Halim


umich1967 menuliskan:
I have a userform with many text boxes, all of which need to be formatted as
#,##0. I can write the code for each one seperately (I have started doing
this), but I thought there might be some coding I can do which will format
them all at once. There are so many text boxes on the form that doing this
seperately would be cumbersome.

Thanks in advance for any help.

Scott Settle

just to show another way, bob's is probably best, because it doesn't matter
how many there are

Dim i As Long
For i = 1 To 7 ' would only work if numbered 1 to 7
With Me.Controls("textbox" & i)
..Text = Format(.Text, "#,##0")
End With
Next

--


Gary


"umich1967" <u26373@uwe wrote in message news:660020f0bfef7@uwe...
I have a userform with many text boxes, all of which need to be formatted
as
#,##0. I can write the code for each one seperately (I have started doing
this), but I thought there might be some coding I can do which will format
them all at once. There are so many text boxes on the form that doing
this
seperately would be cumbersome.

Thanks in advance for any help.

Scott Settle

Gary,

My way is directly if you type the value in a textbox,
and must be added to all of textboxes keyup event in a form.

Bob's way is the same but that format occured after
we run that command

Just choose what u like... <smile

Rgds,

Halim

GKeramidas menuliskan:
just to show another way, bob's is probably best, because it doesn't matter
how many there are

Dim i As Long
For i = 1 To 7 ' would only work if numbered 1 to 7
With Me.Controls("textbox" & i)
.Text = Format(.Text, "#,##0")
End With
Next

--


Gary


"umich1967" <u26373@uwe wrote in message news:660020f0bfef7@uwe...
I have a userform with many text boxes, all of which need to be formatted
as
#,##0. I can write the code for each one seperately (I have started doing
this), but I thought there might be some coding I can do which will format
them all at once. There are so many text boxes on the form that doing
this
seperately would be cumbersome.

Thanks in advance for any help.

Scott Settle

But Halim, the OP stated that doing it for every control would be
cumbersome, which is why I took my course.

Regards

Bob

wrote in message
oups.com...
Gary,

My way is directly if you type the value in a textbox,
and must be added to all of textboxes keyup event in a form.

Bob's way is the same but that format occured after
we run that command

Just choose what u like... <smile

Rgds,

Halim

GKeramidas menuliskan:
just to show another way, bob's is probably best, because it doesn't
matter
how many there are

Dim i As Long
For i = 1 To 7 ' would only work if numbered 1 to 7
With Me.Controls("textbox" & i)
.Text = Format(.Text, "#,##0")
End With
Next

--


Gary


"umich1967" <u26373@uwe wrote in message news:660020f0bfef7@uwe...
I have a userform with many text boxes, all of which need to be
formatted
as
#,##0. I can write the code for each one seperately (I have started
doing
this), but I thought there might be some coding I can do which will
format
them all at once. There are so many text boxes on the form that doing
this
seperately would be cumbersome.

Thanks in advance for any help.

Scott Settle

Hi Bob:

I have entered your code, but I receive an Invalid or Unqualified Reference
error. Should I be placing this in the form initialization (this is where I
put it)?

Thanks for your help :)

Scott

Bob Phillips wrote:
But Halim, the OP stated that doing it for every control would be
cumbersome, which is why I took my course.

Regards

Bob

Gary,

[quoted text clipped - 36 lines]

Scott Settle

--
Message posted via http://www.officekb.com

You should put it in whichever event you want the boxes to be formatted, it
doesn't set a textbox format as it does with a cell. but formats whatever is
in the textbox.

In the code I missed a vital object

For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then
ctl.Text = Format(ctl.Text,"#,##0")
End If
Next ctl


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"umich1967 via OfficeKB.com" <u26373@uwe wrote in message
news:66227da0c9d85@uwe...
Hi Bob:

I have entered your code, but I receive an Invalid or Unqualified
Reference
error. Should I be placing this in the form initialization (this is where
I
put it)?

Thanks for your help :)

Scott

Bob Phillips wrote:
But Halim, the OP stated that doing it for every control would be
cumbersome, which is why I took my course.

Regards

Bob

Gary,

[quoted text clipped - 36 lines]

Scott Settle

--
Message posted via http://www.officekb.com