Form Format

rutherfordcpa
rutherfordcpa used Ask the Experts™
on
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.

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can live to be a hundred if you give up all the things that make you want to be a hundred. - Woody Allen
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
This assumes the textbox is tamed txtOutput

txtOutput.Text = Format(txtOutput.Text, "0,000")

Author

Commented:
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?
Martin LissYou can live to be a hundred if you give up all the things that make you want to be a hundred. - Woody Allen
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Use #,### instead
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Author

Commented:
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.
Martin LissYou can live to be a hundred if you give up all the things that make you want to be a hundred. - Woody Allen
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Can you show me your code?

Author

Commented:
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...
Martin LissYou can live to be a hundred if you give up all the things that make you want to be a hundred. - Woody Allen
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.
Martin LissYou can live to be a hundred if you give up all the things that make you want to be a hundred. - Woody Allen
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Sorry for the confusion but please ignore my last post, I was thinking of VB6.
Martin LissYou can live to be a hundred if you give up all the things that make you want to be a hundred. - Woody Allen
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
In excel VBA, try putting the code in the Exit event for the textboxes.

Author

Commented:
Example of code in the Exit?
Martin LissYou can live to be a hundred if you give up all the things that make you want to be a hundred. - Woody Allen
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Private Sub Acq1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Acq1.Text = Format(Acq1.Text, "#,###")

End Sub

Author

Commented:
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....
Martin LissYou can live to be a hundred if you give up all the things that make you want to be a hundred. - Woody Allen
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What happens if you start with the 3rd one?

Author

Commented:
It doesn't matter which one you start with...  same result after two...

Author

Commented:
Allow me to re-phrase...

It doesn't matter which one you start with...  same result after two entries...
Martin LissYou can live to be a hundred if you give up all the things that make you want to be a hundred. - Woody Allen
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Try this workbook which I created in Excel 2010. When I type in numbers in the 4 textboxes there's no problem.
Book1.xlsm

Author

Commented:
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.

Thoughts?
Martin LissYou can live to be a hundred if you give up all the things that make you want to be a hundred. - Woody Allen
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You should probably start a new question so that others might be able t help.

Author

Commented:
Agreed... just done.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial