Excel Form Number Format

rutherfordcpa
rutherfordcpa used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:
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 LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
How is 55555 getting there? Unload Me should wipe the form clean.
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Author

Commented:
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.
Most Valuable Expert 2011
Top Expert 2011

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

Author

Commented:
Thanks Rorya...  sample code I can use for populating both ways as per your suggestion?
Most Valuable Expert 2011
Top Expert 2011
Commented:
In:
Me.Acq1.Text = Sheets("blah").Range("yadda").Text

Open in new window

Out:
Sheets("blah").Range("yadda").Value = Me.Acq1.Text

Open in new window


Shake it all about: if your textbox contains date information, use:
Sheets("blah").Range("yadda").Value = CDate(Me.Acq1.Text)

Open in new window

Author

Commented:
You solved all of the problems!  Thanks.

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