Formatting UserForm Text Field as Numeric

[Paul D. Simon]

I have a simple Excel database containing 12 columns and about 3000
records.
One of the columns represents a simple Price field in which the
numbers are formatted for 2 decimal places using the format
"#,##0.00". (This is not a calculated field.)
I have created a UserForm containing text fields to match the fields
in the database, and when I initiate the 1-line code "UserForm1.Show",
the UserForm appears on screen with the text fields reflecting the
information from whatever record the cellpointer is on when I initiate
that 1-line macro.
It works perfectly except for the format of the text field
representing the Price field. If the last digit of the number in the
Price field ends in a zero (for example 79.50), the text field in the
UserForm drops that zero and displays 79.5.
Is there any way to have that UserForm text field formatted as
"#,##0.00" so that it will show 79.50 rather than 79.5?

 

[dman]

TextBox1.Value = Format(79.5, "#,###.00")

 

[Jim Rech]

The text in a textbox, even if it looks like a number, is just text. A
textbox has no knowledge of numbers or number formatting. Your code that
populates the textbox is probably something like this:

TextBox1.Value = Range("A1").Value

The "value" of a cell is does not consider its formatting.

If you want the textbox to hold the visual form of the cell's value try
something like this:

TextBox1.Value = Range("A1").Text

Bear in mind though that if the user edits the textbox the formatting will
not be automatically updated.

--
Jim
|I have a simple Excel database containing 12 columns and about 3000
| records.
| One of the columns represents a simple Price field in which the
| numbers are formatted for 2 decimal places using the format
| "#,##0.00". (This is not a calculated field.)
| I have created a UserForm containing text fields to match the fields
| in the database, and when I initiate the 1-line code "UserForm1.Show",
| the UserForm appears on screen with the text fields reflecting the
| information from whatever record the cellpointer is on when I initiate
| that 1-line macro.
| It works perfectly except for the format of the text field
| representing the Price field. If the last digit of the number in the
| Price field ends in a zero (for example 79.50), the text field in the
| UserForm drops that zero and displays 79.5.
| Is there any way to have that UserForm text field formatted as
| "#,##0.00" so that it will show 79.50 rather than 79.5?

 

[Sam Wilson]

Design the form in your visual basic window - right click the text box and
got to properties. There, you'll find 'format', where you can type "#,##0.00"

Sam

 

[Paul D. Simon]

Thank you all for your fast responses.

Since the text fields in the UserForm must reflect the information for
whatever record I'm on at the time I do UserForm1.Show, I cannot use
something like "TextBox1.Value = Range("A1").Value" since that would
always point to cell A1. Instead, here is the section of my code that
accomplishes this for me. It's in View Code when I right-click on the
UserForm in the VBE.

(Again, this is just the section that applies to what I'm talking
about.)

============================
Option Explicit

Public CurrentRow 'current row
Public NumCols 'number of columns
Public LastRow 'last row of data
Public StartRow ' First row with data
Public StartCol ' First column with data
Public RecordCount 'No. of records
Public UndoRow 'row that was inserted or deleted
Public APPNAME As String
Public Initializing As Boolean


range(Cells(CurrentRow, StartCol), Cells(CurrentRow, StartCol +
NumCols - 1)).Select


Price.ControlSource = Cells(CurrentRow, StartCol + 8).Address
============================


One way I have found to get around this problem is to add a helper
column within the database itself giving it the name TextPrice. I
then enter the formula
=TEXT(G2,"#,##0.00"), which I then copy down the appropriate number of
rows. I then altered my code so that the Price text field in the
UserForm now refers to that helper text column rather than the numeric
Price column. I would have preferred resolving the problem within the
UserForm rather than the database in the worksheet, but it does solve
the problem. However, if any of you can come up with something I can
institute within the UserForm so as to eliminate the need for the
helper column, I'd appreciate it.

To Sam: I tried your suggestion but couldn't find "Format" in
properties.

Thanks again to all of you.