Text Box number format with Control Source

**McNultyK** · 10-08-2010, 04:32 PM

I have created a User Form with about 60 Text Boxes. The text boxes are all linked to Control Source cells in a sheet of my workbook. Changing the number in the text box changes the number in the control source cell and vice versa. Some of the text boxes take inputs from the user. Other text boxes display calculated results in the User Form. I don't like the format of the numbers in the text boxes for which the displayed results are calculated. In some cases, way too many significant digits are displayed (e.g., 0.0099999999 instead of 0.0100 or 600.6784536 instead of 600.68 or 0.0000003614564 instead of 3.61E-07). When the text box is NOT linked to a control source cell, I can format the number by code suce as TextBox1 = Format(Expression, "0.00E+00"). But when the text box is linked to the control source cell, it ignores the format code. How can I specify the number format of a text box that is likned to a control source cell? Thanks.

**Leith Ross** · 10-08-2010, 04:51 PM

Hello McNultyK,

Here is an example using TextBox1. Change TextBox1 to match you control's name.

Please Login or Register  to view this content.

**McNultyK** · 10-09-2010, 10:40 PM

Thanks, Leith. The code you provided worked fine. It sets the text box number format the same as the control source format. You don't know how many hours I wasted trying to figure this out. Two questions:
1. What resource materials could I have consulted to find this answer? I didn't find anything on this in the MS help files.
2. Is there a simple way to set a general number format that most text boxes would use or is it necessary to write a private sub for each text box?

Thanks again,
Ken McNulty

**Leith Ross** · 10-10-2010, 04:24 AM

Hello Ken,

Question 1. What resource materials could I have consulted to find this answer? I didn't find anything on this in the MS help files.

Answer 1. Using the web is best resource. I have learned these obscure techniques by writing code for many years. The MS help files will only cover the very basics of the object model. The rest is basically learned from others, books, trial and error.

Question 2. Is there a simple way to set a general number format that most text boxes would use or is it necessary to write a private sub for each text box?

Answer 2. Each control has to be formatted separately, if you want a display format other than a basic string in the Text Box. Because these are ActiveX Controls (event driven), the format statement will have to be called from or added to an event. There is no general property to format a Text Box a certain way.