Avatar of rutherfordcpa
rutherfordcpa
Flag 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.

Thanks.
Microsoft Excel

Avatar of undefined
Last Comment
rutherfordcpa

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Martin Liss

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
rutherfordcpa

ASKER
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 Liss

Use #,### instead
rutherfordcpa

ASKER
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Martin Liss

Can you show me your code?
rutherfordcpa

ASKER
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 Liss

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Martin Liss

Sorry for the confusion but please ignore my last post, I was thinking of VB6.
Martin Liss

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

ASKER
Example of code in the Exit?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Martin Liss

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

End Sub
rutherfordcpa

ASKER
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 Liss

What happens if you start with the 3rd one?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
rutherfordcpa

ASKER
It doesn't matter which one you start with...  same result after two...
rutherfordcpa

ASKER
Allow me to re-phrase...

It doesn't matter which one you start with...  same result after two entries...
Martin Liss

Try this workbook which I created in Excel 2010. When I type in numbers in the 4 textboxes there's no problem.
Book1.xlsm
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
rutherfordcpa

ASKER
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 Liss

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

ASKER
Agreed... just done.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.