Exporting UserForm Textbox input in numeric or currency format with VBA in Excel 2010

[Denny57]

Hi I hope some one can help me with this problem.


I have created a user form to log sales records in to an excel spreadsheet. The form I have created will copy keyed informtion in to the next available row on a worksheet using the follwoing code.


Private Sub cmdExport_Click()


ActiveWorkbook.Sheets("nline Shop Sales").Activate
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = cboDate.Value
ActiveCell.Offset(0, 1) = cboMonthYear
ActiveCell.Offset(0, 2) = txtOrderCount
ActiveCell.Offset(0, 3) = txtOrderNumber
ActiveCell.Offset(0, 4) = cboOrderType
ActiveCell.Offset(0, 5) = cboPaymentMethod
ActiveCell.Offset(0, 6) = cboDeliveryMethod
ActiveCell.Offset(0, 7) = txtGoodsAmountGross
ActiveCell.Offset(0, 10) = txtTotalPostages
ActiveCell.Offset(0, 17) = txtTotalVAT
Range("A5").Select
End Sub


The excel cells which receive the textbox records in BOLD are set to "Currency" format. When the details are exported to these cells, the information is treated as "Text".


I have tried setting the format in the Textbox using the following "solutions" I have found on the internet


1) TextBox1 = Format(TextBox1.Text, "$ #,##0.00")


2) Setting the export value as CDbl or CDec


3) Setting ASCII numeric values to the input details


None work.


I am hoping that there must be some list of coding details that will indicate how textboxes or command buttons should be coded so that information can be exported into excel cells in different formats (Numeric, Currency, Date etc) and not be received and shown as "Text".


Any help would be most grateful..


Incidentally, I created a similar form in an earlier version of Excel and this readily accepts textbox values and converts them to currency format in the excel cells without the need for any additional coding(Including when opened in Excel 2010)!|