200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
hi
I have user form connects with data sheet so when run the userform shows the data but the problem doesn't show numbers format as in textboxes . I searched in the internet to find how do that but not succeed so far
this what I try doing in listbox for COL 6,7,8
.List(I, 6) = (Format(Val(.List(I, 6)), "$#,##0.00"))
.List(I, 7) = (Format(Val(.List(I, 7)), "$#,##0.00"))
.List(I, 8) = (Format(Val(.List(I, 8)), "$#,##0.00"))
Maklil,
Train to differentiate exactly between numbers and text. 1 is a number. "1" is text. You can do calculations with numbers but not with text.
A "TextBox" contains text. So, when you assign the number 1234.56 to the Value property of a textbox that "Value" is actually converted to "1234.56". In that format, a string fed into a clculation will often (but not always) be understood as a number. If you want reliable code, convert the text to a number explicitly with code like VAL("1234.56").
Format(1234.56, "#,##.0.00") converts the number to a string, like "1,234.56". VAL("1,234.56") will return 1 which is the value of the string up to the first non-numeric character, which is the comma. So, the rule is that an unformatted numeric string could be converted to a number either implictly or explicitly but a formatted namber cannot. So, you might use code as shown below.
Dim MyNumber As Double
MyNumber = Val(Replace("1,234.56", ",", ""))The code below will format the cell's value as you type into it.
Private Sub TextBox1_Change()
With TextBox1
.Value = Format(Replace(.Value, ",", ""), "#,##0.00")
End With
End Sub
When you read numbers from an Excel cell you can enter them as formatted text in a listbox with code like Format(Range("C6").Value, "#,##0.00"). Bear in mind that a listbox, like a textbox, can only contain text, no numbers. So, if your listbox holds formatted numbers they get transferred as such to your textbox. But when you extract them back to Excel you must convert the strings to numbers.