Autofill date by referring only one number with ex

Question

What if we could fill a date by only referring a number ? This type of feature would improve user-friendliness in some excel scenarios.

For instance : In March sheet, when I type "7" in a given dates column, the cell in which I inserted the value would return "07/03/19" (or 03/07/19).

If possible, this means I need to specify in the VBA code the month and year for this sheet, and change this variable for every single sheet (february etc.). If the sheet names are months names (Eg "March"), there could even be a way to do it with a one solution VBA code. The following formula takes advantages of it, so I guess VBA could do it to.

=MONTH(DATEVALUE(MID(CELL("filename";$A$1);FIND("]";CELL("filename";$A$1))+1;255)&" 1"))

Enter this formula in a sheet named "March" and it will return "3".

I have been looking for a simple way to do this, but there is none to my knowledge (until you bring your light :)). Data validation feature won't help as far as I know. Also, it's important that the cell in which the number is inserted autofill itself (not another cell).

Is this at least possible ? I bet yes. I've been told to look at event function, but I know too little with VBA.

Answer 1

This may need modified to fit your needs, but maybe a solution like this using the Worksheet_Change event.

Worksheet Change portion:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo SafeExit:
    Application.EnableEvents = False
        If Target.Cells.Count = 1 Then ' 
            If Not Intersect(Target, Me.Columns("A")) Is Nothing Then 'change as needed
                Target.Value = DateFromDay(Me.Name, Target.Value)
            End If
        End If
SafeExit:
    Application.EnableEvents = True
End Sub

Main Function

Public Function DateFromDay(monthName As String, dayNum As Integer) As Date
    On Error Resume Next
    DateFromDay = DateValue(dayNum & " " & monthName & " " & Year(Now()))
End Function

You might consider the Workbook_SheetChange event as well to add this functionality to multiple sheets.