Format userform Textbox for currency

[ipbr21054]

Morning.

On my userform im trying to format Textbox5 to show currency

I have tried the following but when i wish to type say £55.00 i see as i type £5.00 then £5.01

TextBox5 = Format(TextBox5, "£#,##0.00")

 

[saboh12617]

Hello,
Did you check your numbers format separators in Excel settings ? It seems to me it could be misinterpreting the commas/points separtors.
I suggest you select a "basic" currency format in the Excel drop down list, adapt it to your needs, and then copy/paste it in the UF.

 

[ipbr21054]

This is on a userform & textbox.
When i type the veiw is incorrect.
I cant see how to format it so i assume it must be hardcoded & not rightclick select format option

 

[saboh12617]

Yes but the userform share settings with Excel normally. Anyway, maybe you can look on this setting : Application.DecimalSeparator Application.DecimalSeparator property (Excel) to ensure the error is not related to the number format i was mentioning.

 

[ipbr21054]

Didnt see that was the issue.

 

[NoSparks]

this works for me

Private Sub TextBox5_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(TextBox5) = 0 Then Exit Sub
    If IsNumeric(TextBox5) Then
        TextBox5 = Format(TextBox5, "£ #,##0.00")
    Else
        MsgBox "Please enter a monetary value"
        TextBox5 = ""
        Cancel = True
    End If
End Sub

 

[ipbr21054]

The best i could get was the following code below to produce £123 as opposed to £123.00

TextBox5.Value = Format(TextBox5.Value, "£###,##")

I have now used you advised code & it works.
Thanks