Date is driving me nuts

[built4thekill]

Hi All. I'm having an issue which is driving me crazy!! I'm sure the solution is easy but I cant find it. Basically I have a cell in which I'm supposed to type in a date then a corresponding cell will give me a DoB. Now whenever I type in the date in UK format it keeps changing it to US! I've tried changing the format to Date - English UK and also Custom - dd/mm/yyyy but it still changes it to US each time. E.g. 03/04/1980 (3rd April) would auto change to 04/03/1980 (4th March). Any help would be really appreciated!!

 

[RoryA]

Your change event will change all cells to capitals - is that intentional? (It's also the cause of your current problem)

 

[built4thekill]

Is that macro set to act on the cell where you are entering the date?

yes, it actually applies to the whole sheet...

 

[built4thekill]

Your change event will change all cells to capitals - is that intentional? (It's also the cause of your current problem)

No I actually only need it to apply to columns A to Z. I think you might be right! How can I change to code so it applies only to columns A to Z? the date cell is in column AB so hopefully wont be affected.

Current code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.HasFormula Then Exit Sub
        Application.EnableEvents = False
        Target = UCase(Target.Cells(1))
    Application.EnableEvents = True
End Sub

 

[RoryA]

I'd change it to something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Intersect(target, range("A:Z")) is nothing then exit sub
    On Error Resume Next
    Application.EnableEvents = False
    Dim cell As Range
    For Each cell In Target.Cells
        If Not cell.HasFormula And Not IsNumeric(cell.Value2) Then cell.Value = UCase(cell.Value)
    Next cell
    Application.EnableEvents = True
End Sub

 

[built4thekill]

Can you post any code that you have on the sheet (right click the sheet tab and click view code).
Can you also format the cell as a number and post the number it returns please.

Sorry @MARK858 i just noticed you asked this. 03/04/1980 comes up as 29314 in number format

 

[built4thekill]

Your change event will change all cells to capitals - is that intentional? (It's also the cause of your current problem)

You were right. Made the changes and all good. thanks a lot!!!