Writing a Currency value to a Worksheet - Not working on Worksheet

[Pacman52]

Hi all - please could someone let me know where I'm going wrong as its driving me mad trying to figure it out.

So I have a userform and the user enters the Total Sale Amount and once exiting the textbox the figure is formatted as a currency which is what I need it to do and it works fine.

textboxTotalSaleAmount.Value = Format(textboxTotalSaleAmount.Value, "£0.00")

When the user has completed the rest of the boxes on the form and clicks the save button the data is written to the worksheet - again this works fine BUT the worksheet is not recognising the Total Sale amount as a currency (although it does show the '£' symbol). Subsequently the overall Total Sales amounts are not summing on the worksheet.

Does anyone have any idea what I'm missing or doing wrong here?

Many thanks

Paul

 

[KOKOSEK]

It is not summing because £5.80 is a string not a number.
Into worksheet cells You have to write 5.80 (and set displaying as currency) then summary will work correct.
Maybe something like this

Dim ValueToSaveIntoSheet as long
ValueToSaveIntoSheet = textboxTotalSaleAmount.Value
textboxTotalSaleAmount.Value = Format(textboxTotalSaleAmount.Value, "£0.00")

 

[dmt32]

Hi,
what comes out of a textbox is a string.
When posting to your range you need to coerce to the required datatype using appropriate type conversion function & then apply range.numberformat to display the required format

example.

      With Range("B10")
            .Value = CCur(Me.textboxTotalSaleAmount.Value)
            .NumberFormat = "£0.00"
         End With

Dave

 

[Pacman52]

Thanks so much for the quick reply - sadly that doesn't sort the issue as I've just tested it by entering 11 on the textbox and writing the record to the WS and it still doesn't recognised the value as a currency. I checked the column on the actual WS and it is formatted correctly.

 

[Pacman52]

Hi,
what comes out of a textbox is a string.
When posting to your range you need to coerce to the required datatype using appropriate type conversion function & then apply range.numberformat to display the required format

example.

      With Range("B10")
            .Value = CCur(Me.textboxTotalSaleAmount.Value)
            .NumberFormat = "£0.00"
         End With

Dave

Thanks Dave I'll give that a try - I'm assuming I'd put it in the code for the save button as its referencing the range?

 

[dmt32]

Thanks Dave I'll give that a try - I'm assuming I'd put it in the code for the save button as its referencing the range?

Use in code you post textbox value to the range.
Do be aware using the function your textbox must not be empty otherwise you will get an error.

Dave

 

[Pacman52]

Thanks Dave - noted on the textbox not being empty but I've coded that to make sure a user has to put a value in it.

 

[Pacman52]

Just to confirm that works now - I had to re jig the code a bit due to the way I'm writing the data to the WS but finally I can move forward.

Thanks again for the responses.

 

[dmt32]

Just to confirm that works now - I had to re jig the code a bit due to the way I'm writing the data to the WS but finally I can move forward.

Just to add, you would do exactly the same for data types like Dates

Glad resolved & appreciate your feedback

Dave