Date keep changing its format

[hsolanki]

Hello i have userform and textbox 14 you type a date e.g. 29/12/2020 and when it adds on to the sheet it is keep changing it to 12/29/2020. i even set the cells as short date also using the custom format change to DD/MM/YYYY however when userform adds the data its keep changing to MM/DD/YYYY

can somebody please help me.

i want to textbox 14 to add the date format only DD/MM/YYYY

 

[Joe4]

Please post your VBA code that transfers the data from the User Form to the Worksheet.

 

[hsolanki]

Hi Joe Thank you for your quick reply. please see below code which is placed in command button in userform.

Private Sub CommandButton5_Click()
Application.EnableCancelKey = xlDisabled

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Attendance")
Dim n As Long
Dim answer As String


Set myWb = ThisWorkbook


On Error GoTo errHandler:



n = sh.Range("A" & Application.Rows.Count).End(xlUp).Row


sh.Unprotect "1234"

answer = MsgBox(Me.TextBox15.Value & Chr(10) & Chr(10) & Chr(10) & "Would u like to add the above WJ staff absence?", vbQuestion + vbYesNo, "Confirm to Add")

                       If answer = vbNo Then
                       Exit Sub
                        End If
                        
Application.ScreenUpdating = False
Application.EnableEvents = False

sh.Range("A" & n + 1).Value = Me.TextBox14.Value ' this is the textbox which has date typed and sent to column A in sheet
sh.Range("B" & n + 1).Value = Me.ComboBox5.Value
sh.Range("C" & n + 1).Value = Me.TextBox34.Value
sh.Range("D" & n + 1).Value = Me.TextBox15.Value
sh.Range("E" & n + 1).Value = Me.ComboBox6.Value
sh.Range("F" & n + 1).Value = Me.TextBox16.Value
sh.Range("G" & n + 1).Value = Me.TextBox17.Value
sh.Range("H" & n + 1).Value = Environ("username")



'''''''''' Clear boxes
Me.TextBox14.Value = ""
Me.ComboBox5.Value = ""
Me.ComboBox6.Value = ""
Me.TextBox15.Value = ""
Me.TextBox16.Value = ""
Me.TextBox17.Value = ""
Me.TextBox34.Value = ""

sh.Protect "1234"

MsgBox "Updated Successfully!!!", vbInformation

Unload Me

Application.EnableEvents = True
Application.ScreenUpdating = True

Worksheets("Attendance").Activate
Worksheets("Attendance").Cells(1, 3).Select

Exit Sub

errHandler:
 MsgBox "An Error has Occurred  " & vbCrLf & "The error number is:  " _
           & Err.Number & vbCrLf & Err.Description & vbCrLf & _
           "Please Contact Admin", vbCritical, "Error Message"

End Sub

 

[Joe4]

One way would be to add a new line after this line:

sh.Range("A" & n + 1).Value = Me.TextBox14.Value ' this is the textbox which has date typed and sent to column A in sheet

like this:

sh.Range("A" & n + 1).NumberFormat = "dd/mm/yyyy"

 

[hsolanki]

Hi Joe, tried your suggestion, it doesn't work. problem is i have got another useform which finds value based on date and name. userform shows the date as MM/DD/YYYY even though the date in column A it is DD/MM/YYYY but userform shows as MM/DD/YYYY and therefore it wont shows any values in the listbox

this is the code for the userform.

Private Sub UserForm_Initialize()

Dim sh As Worksheet
Dim X, a, b As Long, c As Variant
Dim h As Variant

Set sh = Worksheets("Attendance")
Application.ScreenUpdating = False
'Unique Records
For X = 2 To Cells(Rows.Count, 8).End(xlUp).Row
If WorksheetFunction.CountIf(sh.Range("H5:H" & X), Cells(X, 8)) = 1 Then
ComboBox1.AddItem Cells(X, 8).Value
End If
Next
'Alphabetic Order
For a = 0 To ComboBox1.ListCount - 1
  For b = a To ComboBox1.ListCount - 1
        If ComboBox1.List(b) < ComboBox1.List(a) Then
c = ComboBox1.List(a)
    ComboBox1.List(a) = ComboBox1.List(b)
    ComboBox1.List(b) = c
       End If
  Next
  Next
        With ListBox2
    ListBox2.ColumnCount = 8
    ListBox2.ColumnWidths = "70;80;80;80;80;80;95;70"
    .Top = Me.ListBox1.Top - 30
    .Height = 20
    .Font.Bold = False
    .Font.Name = "Tahoma"
    .Font.Size = 8
    .List = Sheets("Attendance").Range("A3:H3").Value
    End With
  
UserForm4.Height = 150
Application.ScreenUpdating = True

Private Sub CommandButton1_Click()
Application.EnableCancelKey = xlDisabled
    ' Validate Dates
    Dim startDate As Date
    Dim endDate As Date
    startDate = CDate(IIf(TextBox1.Value = vbNullString, 0, TextBox1.Value))
    endDate = CDate(IIf(TextBox2.Value = vbNullString, 0, TextBox2.Value))
    If startDate = 0 Then
        MsgBox "You need to select your First Day off", vbCritical, "Beginning dates"
        Exit Sub
    End If
    
       If endDate = 0 Then
        MsgBox "You need to select your Last day off", vbCritical, "End Date"
        Exit Sub
    End If
    
    ' Validate product
    Dim productName As String
    productName = ComboBox1.Value
    If productName = vbNullString Then
        MsgBox "Please choose a Supervisor", vbCritical, "Select Supervisors"
        Exit Sub
    End If
    
    ' Prepare listbox
    Dim dataListBox As MSForms.ListBox
    Set dataListBox = Me.ListBox1
    
    With dataListBox
        .Clear
         .ColumnCount = 8
          .Top = 122
            .Font.Size = 10
            .IntegralHeight = False
        AddDataToListBox dataListBox, productName, startDate, endDate
    End With
    
    With ListBox1
       '.Height = .Height + .Font.Size + 2
        .Top = Me.ListBox1.Top + 10
       ListBox1.ColumnWidths = "70;80;80;80;80;80;85;70"
    .IntegralHeight = True
    End With
    
End Sub

Private Sub AddDataToListBox(ByVal listBoxControl As MSForms.ListBox, ByVal productName As String, ByVal startDate As Date, ByVal endDate As Date)
    ' Set a reference to the vehicles worksheet
    Dim AttendanceSheet As Worksheet
    Set AttendanceSheet = ThisWorkbook.Worksheets("Attendance")
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    ' Get last row
    Dim lastRow As Long
    lastRow = AttendanceSheet.Cells(AttendanceSheet.Rows.Count, "A").End(xlUp).Row
    
    ' Set range to evaluate
    Dim sourceRange As Range
    Set sourceRange = AttendanceSheet.Range("A5:A" & lastRow)
    
    ' Loop through each cell in range
    Dim sourceCell As Range
    For Each sourceCell In sourceRange.Cells
    
        ' Check if source cell is between dates
        If sourceCell.Value >= startDate And sourceCell.Value <= endDate Then
        
            ' Check if product matches
            If sourceCell.Offset(0, 7).Value = productName Then
                
                ' Begin a counter to add list items
                Dim counter As Long
                
                With listBoxControl
                Dim X, d, yuk, mak As Integer
                For X = 1 To 15
                DoEvents
                If E = 0 Then
                d = d + 9
                yuk = 70
                End If
                UserForm4.Height = yuk + d
                Next
                    .AddItem
                    .List(counter, 0) = sourceCell.Offset(0, 0).Value
                    ' You're missing one column here (is it on purpose?)
                    .List(counter, 1) = sourceCell.Offset(0, 1).Value
                    .List(counter, 2) = sourceCell.Offset(0, 2).Value
                    .List(counter, 3) = sourceCell.Offset(0, 3).Value
                    .List(counter, 4) = sourceCell.Offset(0, 4).Value
                    .List(counter, 5) = sourceCell.Offset(0, 5).Value
                    .List(counter, 6) = sourceCell.Offset(0, 6).Value
                    .List(counter, 7) = sourceCell.Offset(0, 7).Value
                    
                End With
                
                counter = counter + 1
                
            End If
        
        End If
    
    Next sourceCell
    
    If counter = 0 Then
    MsgBox "No new record found!", vbExclamation, "No Records"
    Exit Sub
    
    Else
    MsgBox Me.ListBox1.ListCount & Space(4) & "New Records Found", vbInformation, "New Records Found"
    End If
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

 

[Joe4]

Do you happen to be using a European version of Excel?
If so, note that Excel VBA uses the American date format, "mm/dd/yyyy".

What I would recommend is putting break points in your VBA code, so you can step through it, and watch what is happening on your sheet, each step of the way, and note where things seem to be getting off-track.

 

[Norie]

Use DateValue or CDate to convert the 'text' date into a 'real' date when you write it to a cell.

 

[hsolanki]

Hi i am not an expert in VBA can you kindly help me where m i going wrong. i have uploaded an sample book.

Sample Book v5.xlsm

drive.google.com

problem is when finding on the history with the Pink button. it wont find any data although dates and name matches when searching in userform4

 

[hsolanki]

sorry i am not sure however i have checked the setting and it is UK English and i only problem with this sheet all the rest sheet are ok as i have got multi sheets

 

[Joe4]

Take a look at Norie's comment. I think he probably identified your issue.

Use DateValue or CDate to convert the 'text' date into a 'real' date when you write it to a cell.