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


New Member
Nov 23, 2015
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
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
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)!|

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics