TextBox formatting (Revisited)

[ToddG]

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

 

[Bob Phillips]

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]

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.

 

[Bob Phillips]

Todd,

Don't give up.

What exactly are your objectives?

--

HTH

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

 

[Tom Ogilvy]

Put the same code in the Initialize event of the userform.

 

[ToddG]

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.

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

That did it Bob. Works perfectly. Thanks a million for all
your help. I really appreciate it.

Todd

 

[Bob Phillips]

Glad about that Todd, and I learnt something.

Told you not to give up<vbg>

Bob