Date changes on worksheet

[ipbr21054]

Morning,

Headers are in row 4
Row 5 is hidden
Column M is for date.
My database range is A-W then down the page.
A new row is created in row 6 & i then start to complete each cell,some i type but some have drop down list.

Im finding that when i enter the date the month & day reverse when i leave the cell.
So this morning i have typed in cell M6 02/06/2020 i leave the cell to see it change to 06/02/2020
As a test i unhide row 5 and type the same date in cell M5 but when i leave the cell it stays the same.

Checking the format for cell M5 is shown in screenshot below.

I cant afford to have dates change like its doing so can you advise a fix or another approach for the date.

Thanks

 

[ipbr21054]

See attached.

6912 M5 6913 M6

 

[jasonb75]

Now that is definitely eliminated, a change being made by vba is the only other cause that I can think of.

@mole999 can you think of any other possible causes?

 

[ipbr21054]

I agree but having looked at the code for this sheet i dont see anything.
I will look about a bit buyt thanks for looking

 

[jasonb75]

If you post your worksheet change code then we can have a look at it for you.

 

[ipbr21054]

OK
See the 2 attacheed codes.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim myStartCol As String
    Dim myEndCol As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myRange As Range

    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    
'   *** Specify columns to apply this to ***
    myStartCol = "A"
    myEndCol = "W"

'   *** Specify start row ***
    myStartRow = 5
    
'   Use first column to find the last row
    myLastRow = Cells(Rows.Count, myStartCol).End(xlUp).Row
    
'   Build range to apply this to
    Set myRange = Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol))
    
'   Clear the color of all the cells in range
    myRange.Interior.ColorIndex = 6
    
'   Check to see if cell selected is outside of range
    If Intersect(Target, myRange) Is Nothing Then Exit Sub
    
'   Highlight the row and column that contain the active cell
    Range(Cells(Target.Row, myStartCol), Cells(Target.Row, myEndCol)).Interior.ColorIndex = 8
    Target.Interior.Color = vbGreen
    Application.ScreenUpdating = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
    Set rng = Intersect(Target, Range("A6:W" & Rows.Count))
    
'   Exit if nothing entered into out target range
    If rng Is Nothing Then Exit Sub
    
'   Loop through all cells in our target range
    Application.EnableEvents = False
    For Each cell In rng
        cell = UCase(cell)
    Next cell
    Application.EnableEvents = True
    
        If Not Intersect(Target, Range("L6:L" & Range("L" & Rows.Count).Row)) Is Nothing And Target.Cells.Count = 1 Then
    
        Application.EnableEvents = False
        
        With Target
            .Interior.ColorIndex = 6
            .Font.Size = 11
            .BorderAround xlContinuous, xlThin
            .Font.Name = "Calibri"
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlVAlignCenter
            
        End With
      
        Application.EnableEvents = True
        
    End If
  End Sub

 

[jasonb75]

I agree with you that there is nothing in there which would do it. Maybe @mole999 will think of something else, failing that, can you upload the workbook for us to look at?

 

[ipbr21054]

I can supply workbook but i dont want all to see it as so much info & dont want to erase out names etc.
I can supply you and mole a copy though ?

 

[jasonb75]

Wait to see what mole says first, forum rules say that all correspondence related to questions has to be in the public area of the forum.
As a moderator, mole will be able to clarify if it is acceptable to supply a copy off forum as long as the discussion remains here.

 

[ipbr21054]

OK
Thanks for that.
I do understand but ive a few sheets in the same workbook & also plenty of private info that i dont want to delete etc.

I understand about making a copy but i would realy want to leave it as it is.

 

[mole999]

we would recommend a cut down data book, hosted on a free public site, so we wouldn't have to register to get a copy