Search code examples
excelvbaworksheet-function

Automatically changing dates to YYYYMMDD format with Worksheet_Change

I'm making an error-proof worksheet for employees in my area. Our system needs to be fed through an excel worksheet. Along with other validations I've already added, I need to convert birthday dates to the format YYYYMMDD as string/number because swiching it on Excel with custom formats that drops an error on the system when uploaded (I guess it still reads dates as DD/MM/YYYY).

So I'm trying to use Worksheet_Change() so when the user inputs any date as DD/MM/YYYY it automatically converts is as the required result.

If Not Intersect(Target, Columns("G")) Is Nothing Then

    On Error Resume Next

    fecnacadd = Target.Address
    fecnac = Target.Value

    fecnacadd = Format(fecnac, "yyyymmdd")

    Columns("G").NumberFormat = "General"

End If

This code oddly works when debugging (shows the 'YYYYMMDD' correctly) but running it as is, gets the macro in a loop (?) for 10 seconds and it deletes the content in the cell.

Any hints in my code are appreciated.

Solution

  • Solved it and it's date format proof (doesn't matter the order of DD or MM). Also added the suggestions in the comments. Thanks!

    If Not Intersect(Target, Columns("G")) Is Nothing Then
    Application.EnableEvents = False
    On Error Resume Next

    fecnacadd = Target.Address
    fecnac = Format(Target.Value, "YYYYMMDD")

    Target.Value = fecnac

    Columns("G").NumberFormat = "General"

    Application.EnableEvents = True
End If