Avatar of rutherfordcpa
rutherfordcpa
Flag for United States of America asked on

Excel Form Number Format

I have a form that has 12 text boxes.  Each input is for entering a number and that text box has a ControlSource to a Named cell.

The form is called up while you are not on the sheet with the named cells.

I need the text box to be properly formatted so that, for example, 55555 is actually 55,555.

The following was suggested for formatting:

Private Sub Acq1_Change()
Acq1.Text = Format(Acq1.Text, "#,###")
End Sub

The formatting works, but once the form is closed, the format is not retained when the form is re-opened.

Also, the form freezes Excel after two inputs.  If I move the form to the same page as the Named cells, there are no issues.  If I do not use the suggested format, I can leave the form on the sheet that I need it on and it woks fine.

Suggestions?
Microsoft Excel

Avatar of undefined
Last Comment
rutherfordcpa

8/22/2022 - Mon
Martin Liss

The formatting works, but once the form is closed, the format is not retained when the form is re-opened.
Are you saying that if you type a number into one of the textboxes it formats properly but after you close and reopen the userform that the number is there but it's not formatted? If so can you tell me how you close and reopen the userform? Closing it via of the 'X' and reopening it should completely clear the textboxes and if you have some button on the userform that simply hides it, you should have no problem when you re-show it.
rutherfordcpa

ASKER
Yes, it is formatted correctly, 55,555, but when you close the form and reopen it is 55555.


The form is closed via:

Private Sub cmdClose_Click()
Unload Me
End Sub

The form should be closed rather than hidden.
Martin Liss

How is 55555 getting there? Unload Me should wipe the form clean.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
rutherfordcpa

ASKER
I assume through from the ControlSource...  the form unloads to the Named cell, then the textbox is populated with that as the Value.  Otherwise, it would be blank.
Rory Archibald

I would suggest that you don't use the ControlSource property at all. Use code to load the data from the cells into the textboxes and to put it back in the cells. If the cells are formatted correctly you can use the Text property of the ranges when reading the data to the textbox and you will get it exactly as it appears in the cell.
rutherfordcpa

ASKER
Thanks Rorya...  sample code I can use for populating both ways as per your suggestion?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Rory Archibald

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
You solved all of the problems!  Thanks.