Forum Discussion
hrh_dash
Jul 16, 2022Iron Contributor
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!
Does it help if you use
Dim CS As Dateand
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
Does it help if you use
Dim CS As Dateand
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- hrh_dashIron Contributor
HansVogelaar , thank you for the assistance! it works perfectly!