User Form field inserts wrong date format

**antoka05** · 03-26-2009, 06:35 AM

If the date is in format dd/mm/yyyy you can try to use this code (here I suppose your data field in form is called 'Textbox1'):

Private Sub Macro1()
   Dim myDate As Date
   myDate = DateSerial(Mid(TextBox1.Text, 7, 4), _
                       Mid(TextBox1.Text, 4, 2), _
                       Mid(TextBox1.Text, 1, 2))
   Range("A1") = myDate
End Sub

Regards,
Antonio

**mudraker** · 03-26-2009, 06:46 AM

The date problem is something users who do not use the American date format have been having to deal with for years

See my reply in this old thread

http://www.excelforum.com/excel-prog...and-forms.html

There is also a little bit more in this thread

http://www.excelforum.com/excel-prog...-workbook.html

**simjambra** · 03-26-2009, 07:18 AM

Thank you for all your suggestions.

Here is a copy of the sheet I am using.

On the Feedback Summary sheet, if you click on the top button, a form appears. The feedback is recorded and then summarised on the other sheet.

The trouble is, I am now trying to work out how to summarise each category for each trainer PER MONTH so when the date goes in, incorrectly it's not gerating the correct date.

Mudraker, I couldn't see those hyperlinks but I will have a search.

Thanks

**Greg M** · 03-26-2009, 07:41 AM

Hi again,

The highlighted addition to your code seems to give the correct result on my system (Excel 2000):

Private Sub cmdOK_Click()
    ActiveWorkbook.Sheets("Feedback Summary").Activate
    Range("A1").Select
    Do
    If IsEmpty(ActiveCell) = False Then
        ActiveCell.Offset(1, 0).Select
    End If
    Loop Until IsEmpty(ActiveCell) = True
    ActiveCell.Value = DateValue(txtDate.Value)
    ActiveCell.Offset(0, 1) = cboCourse.Value
    ActiveCell.Offset(0, 2) = cboTrainer.Value
    ActiveCell.Offset(0, 3) = cboDels.Value
    ActiveCell.Offset(0, 4) = txtCat1.Value
    ActiveCell.Offset(0, 5) = txtCat2.Value
    ActiveCell.Offset(0, 6) = txtCat3.Value
    ActiveCell.Offset(0, 7) = txtCat4.Value
    ActiveCell.Offset(0, 8) = txtCat5.Value
    ActiveCell.Offset(0, 9) = txtCat6.Value
    ActiveCell.Offset(0, 10) = txtComm.Value
    Range("A1").Select
End Sub

Hope this helps - please let me know how you get on.

Regards,

Greg M