How do i amend the date format to "Mmm-YY" and to loop through the Inputbox for a correct dates form

How do I 1. Amend the date format to "Mmm-YY" and to 2. Loop through the Inputbox until a correct dates format are entered in As of now, whenever i enter the dates into the inputbox; the dates ...

excel

Formulas and Functions

Macros and VBA

HansVogelaar

Jul 16, 2022

hrh_dash

Does it help if you use

Dim CS As Date

and

CS = CDate(InputBox("Please indicate contract commencement period, dd/mm/yyyy."))

For example:

Sub Test()
    Dim sum5 As Long
    Dim ws As Worksheet
    Dim CT As String
    Dim CS As Date

    Set ws = Sheet1
    sum5 = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    CT = InputBox("Please indicate contract tenure in months.")
    ws.Range("A" & sum5 + 2).Value = "Contact Term (months)"
    ws.Range("A" & sum5 + 2).Offset(0, 1) = CT
    ws.Range("A" & sum5 + 2).Offset(0, 1).NumberFormat = "General"

EnterDate:
    On Error GoTo ErrHandler
    CS = CDate(InputBox("Please indicate contract commencement period in the form d-mmm-yy."))
    ws.Range("A" & sum5 + 3).Value = "Contract Start/Billing date"
    ws.Range("A" & sum5 + 3).Offset(0, 1) = CS
    ws.Range("A" & sum5 + 3).Offset(0, 1).NumberFormat = "mmm-yy"

    Exit Sub

ErrHandler:
    MsgBox "Please enter a valid date!", vbCritical, "Invalid Date"
    Resume EnterDate
End Sub