userforms when I format the text box it changes to text

[grapevine]

I have a macro to format the cell in the user form as follows

private sub textvaluation_change()
txtvaluation = format(txtvaluation, "£#,##0")
end sub

Iam obviously doing something wrong as this converts the numbers to text. Is there anyway I can get the text box to display the entry for money, but keep the formatting as numeric?
many thanks

 

[storm8]

try

txtvaluation.value = format(txtvaluation.value, "£#,##0")</pre>

 

[Norie]

Textbox contain, as the name suggests, text so you aren't really converting from numeric to text.

If you want to convert a value in a textbox to a numeric value to use in calculations etc then you can use the various conversion functions VBA has available.

In your example you might want to use CCur which converts to currency and can handle currency symbols.

 

[grapevine]

I find that not formatting as all keeps the input as a raw number which is formatted correctly once it hits the excel spreadsheet, b8ut I wanted to find a way that the pound symbol or at least the comma separators showed in the text box when the user inputs the information as the user is inputting large numbers and it is easier to see that you have input 11,250,000 correctly rather than 1125000. I tried the suggestion by Storm8 but although it kept the data as a number the user form did not show the formatting so I didnt actually gain anything
How do I use CCur?

txtvaluation.value = CCur(txtvaluation.value, "£#,##0")
txtvaluation.value = format(ccur(txtvaluation.value, "£#,##0")

I tried both of the above examples but the system does not like this
Many thanks

 

[storm8]

This one is tricky, you can to do somethig like this

Option Explicit


Dim Val As Variant 'value
Dim dEvents As Boolean 'disable custom Events


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    dEvents = True
    TextBox1.Value = Format(Val, "L#,##0")
    dEvents = False
End Sub


Private Sub TextBox1_Change()
    If dEvents = True Then Exit Sub
    Val = TextBox1.Value
End Sub

then of course you'll have to parse the numbers from that string if you'll try to change the value