Link to home
Start Free TrialLog in
Avatar of rutherfordcpa
rutherfordcpaFlag 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.

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

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


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.
How is 55555 getting there? Unload Me should wipe the form clean.
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.
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.
Thanks Rorya...  sample code I can use for populating both ways as per your suggestion?
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland 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
You solved all of the problems!  Thanks.