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
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.
Hi there,
Could you post a sample workbook with detail of what you'd like the output to be in whatever scenarios likely?
MArk
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.