Forum Discussion

hrh_dash's avatar
hrh_dash
Iron Contributor
Jul 16, 2022
Solved

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 will always be entered wrongly.

 

For eg, when a date 01/04/2020 (dd/mm/yyyy format) in entered into the inputbox, the outcome would be Jan-20

 

Dim sum5 As Long
Dim ws As Worksheet
Dim CT As String
Dim CS As Variant

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

ws.Range("A" & sum5 + 2).Value = "Contact Term (months)"
ws.Range("A" & sum5 + 3).Value = "Contract Start/Billing date"

CT = InputBox("Please indicate contract tenure in months.")
CS = InputBox("Please indicate contract commencement period, dd/mm/yyyy.")

ws.Range("A" & sum5 + 2).Offset(0, 1) = CT
ws.Range("A" & sum5 + 2).Offset(0, 1).NumberFormat = "General"

ws.Range("A" & sum5 + 3).Offset(0, 1) = CS
ws.Range("A" & sum5 + 3).Offset(0, 1).NumberFormat = "Mmm-yy"

If Not IsDate(CS) Then

ws.Range("A" & sum5 + 3).Offset(0, 1) = ""
MsgBox "Please enter a valid date format Mmm-yy", vbCritical, "Invalid Date"

Else
End If

  

 

 

Appreciate the help provided!

  • 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

2 Replies

  • 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

Resources