I have several textboxes in userforms which either need to be currency or %. I have tried the following code but it only formats correctly when a change is made. Once the form is closed and reopened the formatting is gone. I hope someone can help

Sub TextBox1_OnChang
TextBox1.Value = Format(TextBox1.Value, "$#,##0"
End Su

In advance thanks.

Put your formatting in the UserForm_Initialize() event eg

Sub UserForm_Initialize()
TextBox1.Value = Format(TextBox1.Value, "$#,##0")
End Sub

Cheers
Nigel

"bruce forster" wrote in message
...
I have several textboxes in userforms which either need to be currency or
%. I have tried the following code but it only formats correctly when a
change is made. Once the form is closed and reopened the formatting is
gone. I hope someone can help.

Sub TextBox1_OnChange
TextBox1.Value = Format(TextBox1.Value, "$#,##0")
End Sub

In advance thanks.

What's this OnChange event?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"bruce forster" wrote in message
...
I have several textboxes in userforms which either need to be currency or
%. I have tried the following code but it only formats correctly when a
change is made. Once the form is closed and reopened the formatting is
gone. I hope someone can help.

Sub TextBox1_OnChange
TextBox1.Value = Format(TextBox1.Value, "$#,##0")
End Sub

In advance thanks.

OnChange seems to fire the formatting once the textbox is changed

For activeX controls, to the best of my knowledge,
There is no onchange event. There is a change event and a click event.

--
Regards,
Tom Ogilvy

"bruce forster" wrote in message
...
OnChange seems to fire the formatting once the textbox is changed

Okay. But when I used a _Change code the formatting works but then when I close the userform and reinitialize it goes away. what should I do??

Did you try Nigel's suggestion?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"bruce forster" wrote in message
...
Okay. But when I used a _Change code the formatting works but then when I
close the userform and reinitialize it goes away. what should I do??

Bob

I will try his suggestion but where is the UserForm_Initialize code?? Also, I have about 36 textboxes on the user form, is there any way to format them simultaenously

Also, I will be posting another question this evening and would really appreciate your response. You have been very helpful

Thanks.

Userform_Initialize is jsut an event procedure that goes in the userform.

If the format is all the same, you can do it in a loop like so

Private Sub UserForm_Initialize()
Dim ctl As Control

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

End Sub


A word of warning. If the textbox is linked to a worksheet cell (I assume
this), the formatting shown above is superceded. The only way I could get
around this was with this sort of code

Option Explicit

Dim t46_Controlsource As String
Dim t47_Controlsource As String

Private Sub TextBox46_AfterUpdate()
TextBox46.Text = Format(TextBox46.Text, "#,##0.00")
End Sub

Private Sub TextBox47_AfterUpdate()
TextBox47.Text = Format(TextBox47.Text, "#,##0.00")
End Sub

Private Sub UserForm_Activate()
TextBox46.Text = Format(TextBox46.Text, "#,##0.00")
TextBox47.Text = Format(TextBox47.Text, "#,##0.00")
End Sub

Private Sub UserForm_Initialize()
t46_Controlsource = TextBox46.ControlSource
t47_Controlsource = TextBox47.ControlSource
TextBox46.ControlSource = ""
TextBox47.ControlSource = ""
End Sub

Private Sub UserForm_Terminate()
Worksheets(1).Range(t46_Controlsource).Value = TextBox46.Text
Worksheets(1).Range(t47_Controlsource).Value = TextBox47.Text
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"bruce forster" wrote in message
...
Bob:

I will try his suggestion but where is the UserForm_Initialize code??
Also, I have about 36 textboxes on the user form, is there any way to format
them simultaenously?
P
Also, I will be posting another question this evening and would really
appreciate your response. You have been very helpful.

Thanks.

A textbox is always plain text. Try putting that line in the initialize
event of the form.


---
Message posted from http://www.ExcelForum.com/

Hello Bob

You are correct that the textbox(s) are linked to a worksheet cell and I won't bore you with why I have it set up this way

I have not tried your formatting code suggestion yet but will this evening. Do I have to due a line of code for each textbox?

Also, I have a combobox with several assumption drives and I have to close and reopen the userform to have the changes "fire" is there anyway I can get the changes to occur on screen. Maybe some code to reinitialize the userform after any change with out closing the userform

Thanks for all your help.

Yes you need to do it for each textbox.

What is an assumption drive? You need to explain in more detail what is
happening, as I can only guess at what happens, what you want to happen.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"bruce forster" wrote in message
...
Hello Bob.

You are correct that the textbox(s) are linked to a worksheet cell and I
won't bore you with why I have it set up this way.

I have not tried your formatting code suggestion yet but will this
evening. Do I have to due a line of code for each textbox??

Also, I have a combobox with several assumption drives and I have to close
and reopen the userform to have the changes "fire" is there anyway I can
get the changes to occur on screen. Maybe some code to reinitialize the
userform after any change with out closing the userform.

Thanks for all your help.