Link to home
Start Free TrialLog in
Avatar of rutherfordcpa
rutherfordcpaFlag for United States of America

asked on

Form Format

I am creating a form in VBA.  The user is to enter numerical information, e.g. 600,000.  This amoutn also populates a particular cell which is formatted properly.

When entered on the form, the display is 600000 which is hard to read.

How do I format the form so that it reads 600,000.

Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rutherfordcpa


It appears that when I use the suggested format, 600 is "0,600" rather than 600.

How do I get it to recognize the proper format?
Use #,### instead
Excel also freezes up when I use the code after say the third input (via tab) or so...  I have a menu that uses about 12 inputs...  if I remove the suggested code, everythign is fine.. except for the form formatting.
Can you show me your code?
Private Sub Acq1_Change()
Acq1.Text = Format(Acq1.Text, "#,###")
End Sub

Private Sub Acq2_Change()
Acq2.Text = Format(Acq2.Text, "#,###")
End Sub

and so on...

The #,### resolved the formatting... now just need to figure out why it is freezing...
Besides (perhaps) what you are experiencing there's another problem with having the code in the Change event.

If you type 12345, you will get 5,1234

That can be fixed but you should probably have the code in the LostFocus event.
Sorry for the confusion but please ignore my last post, I was thinking of VB6.
In excel VBA, try putting the code in the Exit event for the textboxes.
Example of code in the Exit?
Private Sub Acq1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Acq1.Text = Format(Acq1.Text, "#,###")

End Sub
Yes, tried that... same thing...  after entering the first box, tabing to the second and entering info it then freezes when you go to the third...

There are twelve boxes (all named differenly)  and it doesn't matter which one you start with.. the third freezes the program....
What happens if you start with the 3rd one?
It doesn't matter which one you start with...  same result after two...
Allow me to re-phrase...

It doesn't matter which one you start with...  same result after two entries...
Try this workbook which I created in Excel 2010. When I type in numbers in the 4 textboxes there's no problem.
I think I have isolated the "freeze" issue.  For each of the 12 boxes in the form, there is a ControlSource.  Thus the data is unloaded to a corresponding "Name".  Using the suggested formatting causes the freeze unless the form is open on the same sheet that the data is being unloaded.  I need the form to open while on a different sheet.... perhaps I have it unload on that screen and then it populates the other sheet.

Turning off EnableEvents and ScreenUpdating does not help.

Eliminating the suggested formatting, I then do not need to be on the same sheet to unload.

Any thoughts on this?

Also, using this formatting is not retained in the form once it is closed.  Re-opening the form and you 55555 rather than 55,555 as it was displayed before closing.

You should probably start a new question so that others might be able t help.
Agreed... just done.