Converting number in textbox to currency after calculation

[coolman]

Hi All

I am designing a user form whereby it would calculate the total cost after performing rate * quantity

However I have encounter difficulties whereby the vba is able to calculate the total cost but could not convert to currency.

Are there anyone who could advise me on where the code has gone worng?

Below is the code

Private Sub TxtAmount_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)


Me.TxtAmount.Value = Me.TxtBoxQuantity * Me.TxtBoxRate
Me.TxtAmount.Value = Format(Me.TxtAmount.Value, "Currency")


End Sub

 

[Norie]

The value in a textbox is text, to format it as currency you need to change the text to a number.

There are various ways to do that, here's one.

Me.TxtAmount.Value = Format(Val(Me.TxtAmount.Value), "Currency")

 

[coolman]

Hi there

Thank you for the response.

I tried your code however it did not work. it stated runtime error.

Are there any other ways?

The value in a textbox is text, to format it as currency you need to change the text to a number.

There are various ways to do that, here's one.

Me.TxtAmount.Value = Format(Val(Me.TxtAmount.Value), "Currency")

 

[Norie]

What was the value in the textbox when you got the error?

 

[L. Howard]

Hi All

Below is the code

Private Sub TxtAmount_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)


Me.TxtAmount.Value = Me.TxtBoxQuantity * Me.TxtBoxRate
Me.TxtAmount.Value = Format(Me.TxtAmount.Value, "Currency")


End Sub

You could try something like this portion of code in red. Originally used here with an InputBox to convert a text value to a number.
In this example I formatted G2 as text and entered a value, say 593, which shows and acts like text in G2.
The values exported to column A are numbers.
You would assign your TextBox value to the Variant aLook.


Howard

Sub Text_To_Num()

Dim aLook As Variant

aLook = Range("G2")   'InputBox("Enter the item to search for")

[COLOR=#ff0000]If aLook = "" Then
    Exit Sub
ElseIf IsNumeric(aLook) Then
    aLook = Val(aLook) '/ converts a "text" number to a value
Else
    '/ is text and that is okay
End If
[/COLOR]
MsgBox aLook
Range("A" & Rows.Count).End(xlUp)(2) = aLook

End Sub

 

[coolman]

When I input the value for quantity = 2 & rate = $11.00 in the individual text box, the total cost textbox = 22 without the decimal place and $ unit. it further prompt the error message RUN TIME ERROR -2147483640 (80000008).

I'm currently using excel mac 2011

What was the value in the textbox when you got the error?