Date keep changing its format

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
124
Office Version
  1. 2010
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,719
Office Version
  1. 365
Platform
  1. Windows
Please post your VBA code that transfers the data from the User Form to the Worksheet.
 

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
124
Office Version
  1. 2010
Platform
  1. Windows
Hi Joe Thank you for your quick reply. please see below code which is placed in command button in userform.

VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,719
Office Version
  1. 365
Platform
  1. Windows
One way would be to add a new line after this line:
VBA Code:
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:
VBA Code:
sh.Range("A" & n + 1).NumberFormat = "dd/mm/yyyy"
 

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
124
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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.

VBA Code:
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

VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,719
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,274
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
124
Office Version
  1. 2010
Platform
  1. Windows
Hi i am not an expert in VBA can you kindly help me where m i going wrong. i have uploaded an sample book.


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

Board Regular
Joined
Jan 16, 2020
Messages
124
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,719
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,061
Messages
5,628,369
Members
416,314
Latest member
Dan99321

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top