Hi Todd,

The crucila bit of information, controlsource.

It seems that when you have ControlSource, any settings don't apply (at all
times).

The best I could come up with to overcome this was to lose the controlsource
property in the code, but I save it so that I can re-write to those cells on
exit.

Here it is

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)

"ToddG" wrote in message
...
Sorry for the confusion Bob, guess i'm just having trouble
explaining it to you.

All of these textboxes are linked to cells in a
spreadsheet via the control source. These cells contain
prices so I would like these textboxes to always be
formatted properly for a price (i.e. 2.50, 3.75, etc.).
The userform is being shown by a custom macro button with
the following code:

Sub Costing()
Load Costing1
Costing1.Show
End Sub

When the userform is shown I would like the textboxes to
show the current values in their corresponding cells and
always have the "price" format. When I run the userform
and enter the prices they are in the correct "price"
format. When I save and close the spreadsheet after making
changes and then open the workbook again to make further
changes, the formatting goes back to the way it originally
was (i.e. 2.5, 3.75). It leaves off the trailing zeros in
other words. Hope this explains it a little better.

Thanks very much,

Todd


-----Original Message-----
Todd,

When you say '...Everything is working fine until I close
the file and open
it back up. The formatting goes back to its original
state ..' can we
dissect what exactly that means.

Are you actually closing the file that the form is in?
Form goes back to its original state - you mean when you
open the form after
re-opening the workbook? Tom and my version should still
maintain the
format, but the values will dis-appear. What exactly do
you want to see
happen.

keep going, we'll get there. We have been here before if
you recall, and we
made it.

Best Regards

Bob

"ToddG" wrote in message
...
Tom and Bob,

I have a simple spreadsheet which contains a list of
prices. The userform is simply an easier way of changing
these values from one location. This userform is
activated
using a macro button in a custom menu. Everything is
working fine until i close the file and open it back up.
The formatting goes back to its original state. Tried
putting the code in UserForm_Initialize and it still
does
it. My apologies if I sounded negative in my earier
post,
i've just spent a lot of time trying to get a stinking
zero in a textbox...lol

Thanks very much for your help, it's truly appreciated.
-----Original Message-----
Put the same code in the Initialize event of the
userform.

--
Regards,
Tom Ogilvy


"ToddG" wrote in
message
...
That did it Bob, but now when I close the workbook
and
open it back up the textboxes go back to their
original
format. I give up. Thanks VERY much for your help, I
really appreciate it.
-----Original Message-----
Todd,

I think you may be unloading the form, rather than
hiding
it

Unload Costing or Unload Me

should be

Hide.Costing or Hide.Me

--

HTH

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

"ToddG" wrote in
message
...
I'm using the following code to format a group of
textboxes on a userform (thanks for your help
Bob):

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

I'm using a macro button with the following code
to
show
this userform:

Sub Costing()
Costing1.Show
End Sub

I think this is where the problem is. If i show
the
userform using this code the formatting will not
work. I
have another userform being shown with a command
button
on
this userform that works fine which leads me to
believe
that it's with the macro button code. Any ideas?

TIA,

Todd


.



.



.