Writing currency to worksheet

[Pacman52]

Hi all. I have a test userform called Userform2

On the userform I have the a textbox called txbAmount. once a user has entered an amount into the textbox I want it to show as the UK currency. I have accomplished this with the following After Update code.

 txbAmount = Format(txbAmount.text, "£#,##0.00")

I then want to write it to a worksheet table so that when its written Excel see's it as a currency and allows me to reference the cell for calculations ect. This is where I am getting stuck as no matter what I've tried It always writes the amount as text and indents it to the left on the worksheet.

I've tried so many different ways to get this to write to the sheet as currency NumberFormat, .value, etc but I've actually forgotten what I've tried and become over confused, so the code below is what I started with (taken from a backup of the workbook)

Private Sub cmbWrite_Click()

 Dim newRow As Long
    With sh1Test

    newRow = .Cells(.Rows.Count, 1).End(xlUp).row + 1

    .Cells(newRow, 8).Value = txbAmount.Value
     
    End With

End Sub

(There are other textboxes on the userform date's, notes etc that are written to the sheet in various columns hence this line writing to Col 8)

Would someone be able to let me know what I'm doing wrong please and how to get whatever amount a user enters on the userform to show on the worksheet as currency.

Many thanks


Paul

 

[Joe4]

The issue is that the VBA FORMAT function is similar to the Excel TEXT function, and it returns a text result (where those currency signs are hard-coded characters in the string).
You do NOT want to use the FORMAT function. You simply want to write the numeric value to the cell, and apply cell formatting to get it to display with the currency symbols, but remain a number.

So to format the cell, use a line like this:

.Cells(newRow, 8).NumberFormat = "£#,##0.00"

 

[dmt32]

Hi,
when posting to the range you can use a Type Conversion Function to coerce text to the required data type

 .Cells(newRow, 8).Value = CCur(txbAmount.Value)

Just be mindful that the function will error if the value entered is not of a valid type.

Dave

 

[Pacman52]

Thanks Joe & Dave for the replies. I will adjust the code when I'm back on Monday and let you know the out come.

Paul

 

[dmt32]

I should have added that CCur recognises currency symbols as well as thousand’s separators embedded in a currency string.
you should therefore, be able to continue to include the currency symbol in your textbox if wanted.

Dave