Excel VBA Date Formatting help

[ExcelGirl1988]

Hello

I hope someone can help as I have been trying to figure this out for the last few days and getting nowhere.

I have a complaints database that I have put together where the user fills in the userform with the details of the complaint and it gets put into the correct tab in the workbook, this part works fine. On the homepage I have set it up so that there is a count of how many complaints are input for each year and this is where it is not working properly.

On the userform there is a date box which I have used vba to format so that when a user enters a date it changes to the correct format (dd/mm/yyyy) but when this is pasted into the workbook from the userform the date format changes to mm/dd/yyyy despite me changing the formatting in each worksheet to the correct date format (UK date) in the date column.

I do not know what to do with this, I hope someone can help me figure this out. I have attached a link to the version of my spreadsheet with no confidential information in it so you can see how it works (or doesn’t work).

Thank you in advance.
Draft Schools Complaint Log

 

[Herakles]

Have you set the data format in the worksheet manually or by using VBA BEFORE you write the value from the userform?

Try formatting the date in the worksheet after you write the date from the userform.

 

[ExcelGirl1988]

Thank you for your reply. I have tried this, I have gone into the worksheet after the userform has copied the data in and formatted the date column but this does not seem to work, I have to manually re-write the date in for it to register in the Count Ifs statement on the home sheet.

 

[Herakles]

I've tried to access the spreadsheet but my email address is not in your employers my.sharepoint.com directory.

I've set up a userform and it works for me.

This was my line.

Range("F10").Value = Format(CDate(Me.txtDate), "dd/mm/yyyy")

 

[ExcelGirl1988]

I've tried to access the spreadsheet but my email address is not in your employers my.sharepoint.com directory.

I've set up a userform and it works for me.

This was my line.

Range("F10").Value = Format(CDate(Me.txtDate), "dd/mm/yyyy")

Hi, sorry I forgot I had saved the workbook in my work sharepoint. The code I have used for the datebox on the user form is:

Private Sub DateBox_AfterUpdate()
If IsDate(Me.DateBox.Text) Then
Me.DateBox.Text = Format(Me.DateBox.Text, "dd/mm/yyyy")
End If
End Sub

Is this where you put your line of code? I used this code to ensure that the date is changed to the correct format after the user has input it.

Then when the user clicks on the 'add' button on the user form this code is run:

Private Sub CMDSubmit_Click()
    Dim emptyRow As Long
    Dim Rng As Range
    Dim WorkRng As Range
    'Make School Sheet active
    On Error GoTo myerror
    With ThisWorkbook.Worksheets(Me.SchoolName.Text)
         .Activate
        emptyRow = WorksheetFunction.CountA(.Range("A:A")) + 1
        If SchoolName.Value = "Other" Then
            .Cells(emptyRow, 1).Value = OtherSchool.Value
        Else
        .Cells(emptyRow, 1).Value = SchoolName.Value
        End If
        If IsDate(Me.DateBox.Text) Then
            Me.DateBox.Text = Format(Me.DateBox.Text, "dd/mm/yyyy")
        End If
        .Cells(emptyRow, 2).Value = DateBox.Value
        .Cells(emptyRow, 3).Value = Source.Value
        .Cells(emptyRow, 4).Value = Reference.Value
        .Cells(emptyRow, 5).Value = Category.Value
        .Cells(emptyRow, 6).Value = tbInsertFile.Value
        .Cells(emptyRow, 7).Value = tbComplainant.Value
        .Cells(emptyRow, 8).Value = SentTo.Value
        .Cells(emptyRow, 9).Value = DealtWith.Value
        .Cells(emptyRow, 10).Value = LADO.Value
        .Cells(emptyRow, 11).Value = ResponseDue.Value
        .Cells(emptyRow, 15).Value = Statuscombobox.Value
    End With

Call ClearForm_Click

On Error Resume Next
Set WorkRng = Application.Selection
Set WorkRng = Application.Range("F2:F100")
For Each Rng In WorkRng
    Application.ActiveSheet.Hyperlinks.Add Rng, Rng.Value
Next
Me.Hide
ThisWorkbook.Worksheets("Home").Activate

myerror:
If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

EDIT: I have added screenshots of what date is input on the userform and what is then input on the school worksheet.

 

[Alex Blakenburg]

Try:

.Cells(emptyRow, 2).Value = CDate(DateBox.Value)

You may also need it here but try either or both:

Me.DateBox.Text = Format(CDate(Me.DateBox.Text), "dd/mm/yyyy")

 

[ExcelGirl1988]

Try:

.Cells(emptyRow, 2).Value = CDate(DateBox.Value)

You may also need it here but try either or both:

Me.DateBox.Text = Format(CDate(Me.DateBox.Text), "dd/mm/yyyy")

Thank you so much, your first suggestion of

.Cells(emptyRow, 2).Value = [B]CDate[/B](DateBox.Value)

worked!! I've been working on this for days lol trying to figure it out Thank you

 

[Alex Blakenburg]

Your welcome. CDate uses your region setting to convert the date.