How to convert a vaue in a userform textbox to reflect a currency in a cell

[KyleOliver]

Hi,

I have a database (excel spreadsheet) with a user form where there are several textboxes where I capture client information and when I click "Submit" all the data entered into the user form gets populated into my data table on the spreadsheet.

The problem I have is that when I enter a currency value into the text box on my user form the value reflects on the data table as a number but not a currency value in the cell of the data table even though i have formatted the cells as currency.
Furthermore, the number that is reflected in the cell on the data table does not form part of the SUM calculation that i have at the bottom of the currency column within the data table.

Please help ?

Regards,
Kyle

 

[Nine Zero]

If you are using

TextBox1.Text to pass the number being input it might not work

Try changing it to TextBox1.Value

other than that i dont see what the issue could be maybe reformat before inputting into spreadsheet?

 

[My Aswer Is This]

Show us how your entering the value in the UserForm Textbox

And show you your script which enter's the value into the cell.
I use TextBox.value and the things work for me if the cell the data goes into is already formatted for currency

 

[KyleOliver]

show us how your entering the value in the userform textbox

and show you your script which enter's the value into the cell.
I use textbox.value and the things work for me if the cell the data goes into is already formatted for currency

Reply;

My user form looks like this;

https://www.dropbox.com/home?preview=User+Form.jpg


The "Retainer Amount" textbox is TxbRetainerAmount
When I enter an amount such as R2000, I enter the currency value as "2000" in TxbRetainerAmount. Then when I click "Continue" the "2000" reflects in my data table in Column 12 cell N8 just as "2000"

https://www.dropbox.com/home?preview=Data+Table.jpg



The code I use is as follows;

Sheets("Data").Range("Data_Start").Offset(TargetRow, 12).Value = TxbRetainerAmount 'Retainer Amount
 TxbRetainerAmount.Value = Format(TxbRetainerAmount.Value, "R #,###,###")

I hope this is sufficient information?

Thanking you in advance,
Kyle

 

[Nine Zero]

wait so you are formatting it AFTER you insert it?

perhaps maybe this

Sheets("Data").Range("Data_Start").Offset(TargetRow, 12).Value = Format(TxbRetainerAmount.Value, "R #,###,###")

 

[My Aswer Is This]

It you enter a value like 3.50 into your textbox then your script should be
Sheets(1).Range("A1").value=Textbox1.value

 

[Norie]

Try this.

Sheets("Data").Range("Data_Start").Offset(TargetRow, 12).Value = Val(TxbRetainerAmount.Value)
 'Retainer Amount
 TxbRetainerAmount.Value = Format(TxbRetainerAmount.Value, "R #,###,###")

 

[KyleOliver]

Try this.

Sheets("Data").Range("Data_Start").Offset(TargetRow, 12).Value = Val(TxbRetainerAmount.Value)
 'Retainer Amount
 TxbRetainerAmount.Value = Format(TxbRetainerAmount.Value, "R #,###,###")

THANK-YOU Norie, you are a LEGEND!!!

All I did was copy and pasted your code over my code and it works!!!!!

Thank-you so much.

Regards,
Kyle