Excel 2013 64bit - VBA Userform date format issue, need dd/mm/yyyy but not displaying correctly

Question

thank you for taking the time to check out my query.

I have 4 date picking calendars in my userform and although the format is set to dd/mm/yyyy, it doesn't display that format in the textbox, it displays as US mm/dd/yyyy. The date picker/Calendar I have used is from the Microsoft site Trevor Eyre's date picker My operating system (Win 8.1) is setup with system date, dd/mm/yyyy and the columns in the spreasheet (where the data from the form is stored) are also formatted as dd/mm/yyyy.

I don't know if I have confused VBA and that is why I am not getting what I expect.

The following code are all from the Userform Sub routine, all relating to date textboxes. Perhaps someone may be able to see the problem straight away .. so here are the sub routines that refer to dates and their format:

Userform Code:

 Private Sub Calendar1_Click()
 ADD_Inc_DATE_TXT.value = CalendarForm.GetDate
 If IsDate(ADD_Inc_DATE_TXT.Text) Then
 Me.LBL_Inc_Day_Type.Caption = Format(ADD_Inc_DATE_TXT.Text, "ddd")

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

Private Sub Calendar2_Click()
 TXT_AssetMgr_DATE = CalendarForm.GetDate
   If IsDate(ADD_Inc_DATE_TXT.Text) Then
    Me.TXT_AssetMgr_DATE.Text = Format(TXT_AssetMgr_DATE.Text, "dd/mm/yyyy")
End If
End Sub


Private Sub Calendar3_Click()
 TXT_LastUserDATE = CalendarForm.GetDate
 If IsDate(ADD_Inc_DATE_TXT.Text) Then
 Me.TXT_LastUserDATE.Text = Format(TXT_LastUserDATE.Text, "dd/mm/yyyy")
End If
End Sub

Private Sub Calendar4_Click()
  ADD_Date_ServiceJobLogged_TXT = CalendarForm.GetDate
  If IsDate(ADD_Inc_DATE_TXT.Text) Then
  Me.ADD_Date_ServiceJobLogged_TXT.Text =  Format(ADD_Date_ServiceJobLogged_TXT.Text, "dd/mm/yyyy")
 End If
 End Sub

All the codes are from the Trevor Eyre's date picker download website. I have tried to follow the directions.

I haven't changed the code except to try to change the date format.

The code pertaining to the label for displaying the actual text day that was added to the coding of Calendar 1 (I don't want it for the other calendars). Me.LBL_Inc_Day_Type.Caption = Format(ADD_Inc_DATE_TXT.Text, "ddd") is working fine. The following code is from the Trevor Eyres located in the Calendar Userform Module

    Sub BasicCalendar()
         dateVariable = CalendarForm.GetDate
          If dateVariable <> 0 Then Range("H16") = dateVariable
     End Sub

I hope I havent confused you too much ...

I really hope someone can help .. I have been struggling with it for the last 4 months and have not been able to fix the format into dd/mm/yyyy.

I'd appreciate it very much. TheShyButterfly

Thank you in advance. Cheers, TheShyButterly.

Gary's Student Gary's Student · Accepted Answer · 2016-05-09T13:11:21

You can input the date as a String and then parse it yourself:

Sub INeedADate()
    Dim s As String, d As Date, outpt As String

    s = Application.InputBox(Prompt:="Please enter a date as dd/mm/yyyy", Type:=2)
    arr = Split(s, "/")
    d = DateSerial(arr(2), arr(1), arr(0))
    outpt = Format(d, "dd/mm/yyyy")
    MsgBox d & vbCrLf & outpt
End Sub

and the output:

As you see, the top of the MsgBox shows the date in my (US) format and the lower part of the MsgBox shows the date as a correctly formatted string.

Excel 2013 64bit - VBA Userform date format issue, need dd/mm/yyyy but not displaying correctly

1 Answers