PDA

View Full Version : VBA Format vs Range.NumberFormat



QuinRiva
06-13-2013, 12:18 AM
I have created an interface that lists a number of inputs on a single input form but have discovered a problem with the way the Format method works.

When the form is displayed, I populate the TextBoxes and ComboBoxes with values from the spreadsheet, and apply the relevant number formatting. This is achieved using:

TextBox1.Value = Format(Range.Value, Range.NumberFormat)

However it turns out that Format does not evaluate the same as NumberFormat. Case in point, the Excel number format:
[=0]"No";[=1]"Yes";"Error"
Evaluates 0, 1, 3 to No, Yes, Error (respectively).
But in VBA this is evaluated to Error, No, No.
Is there anyway to apply a worksheet number format to a textbox/combobox?

p45cal
06-13-2013, 12:44 AM
Not that I'm aware of but then I may not be aware of much.
How about using simply:
TextBox1.Value = Range.Text
?

mancubus
06-13-2013, 01:01 AM
Sub testNumFormat()
MsgBox _
"neg num format= " & Format(-100, "[=0]""No"";[=1]""Yes"";""Error""") & vbLf & _
"0 format= " & Format(0, "[=0]""No"";[=1]""Yes"";""Error""") & vbLf & _
"pos num format= " & Format(100, "[=0]""No"";[=1]""Yes"";""Error""")
End Sub


mb result shows:
negatives evaluates to YES
positives evalue to NO
0s evaluate to ERROR

snb
06-13-2013, 01:45 AM
There's no relation whatsoever between Excel's numberfomat and VBA's Format.

Like P45cal indicated:

Textbox1.Text=cells(x,y).Text

is sufficient.

QuinRiva
06-13-2013, 09:36 PM
There's no relation whatsoever between Excel's numberfomat and VBA's Format.

Like P45cal indicated:

Textbox1.Text=cells(x,y).Text

is sufficient.

I would like the textbox/combobox to behave similarly to a cell; that is, if I enter a 0 or 1 or whaterver, I have an OnExit Event that encodes the number entered into the desired format. Given that everything is dynamic, I have no way of knowing what that numberformat is other than to query the Range.NumberFormat property.

Particularly in the case of a combobox, where the options are statically defined, the items should be stored as the absolute value but be displayed in a way that makes sense. This enables these values to be written back to the spreadsheet.

Writing Yes into a cell that is supposed to accept only an integer, will cause problems.

GTO
06-13-2013, 10:18 PM
Hi there,

Could you post a sample workbook with detail of what you'd like the output to be in whatever scenarios likely?

MArk

snb
06-14-2013, 12:08 AM
The wrting of the value in a Textbox into a worksheet has nothing to do with the way values are being entered into the TextBox or are being represented in that TextBox.
If you want to adapt the Textbox's value to the requirements of the worksheet you can do that in the writng operation.