CODEWITHSUNDEEP
excelvbaruntime-error
Kristen
Kristen

Reputation: 87

Runtime 13 error due to incorrect time entry format

I have code that moves a time entered value to a field using a specific time format. However, when entering the time, if it isn't entered in the correct format, the user gets the Runtime 13 error and then cannot do anything other than close the userform, stop the code from running, and try again. I can format a message to return an error telling the user to correct the entry but what code do I use to put the user back into the field and allow them to make a correction? I am putting the code for this userform textbox here for reference:

Private Sub txtDepartTime_AfterUpdate()
'When time is entered, time transfers immediately to spreadsheet datafield.

Dim TargetRow As Integer
TargetRow = Sheets("Codes").Range("D43").Value + 1

With Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 25)
 .Value = TimeValue(txtDepartTime)
 .NumberFormat = "hh:mm" 'departure time

 End With

End Sub

Upvotes: 1

Views: 49

Answers (1)

Michael
Michael

Reputation: 4848

Start by testing whether txtDepartTime can be converted to a time. If it can't, then notify the user and exit execution, so that they can re-enter the time.

Private Sub txtDepartTime_AfterUpdate()
'When time is entered, time transfers immediately to spreadsheet datafield.

Dim TargetRow As Integer, TestTime As Date

On Error Resume Next
TestTime = TimeValue(txtDepartTime)
On Error GoTo 0

If TestTime = 0 Then
    MsgBox "Time entered is not valid. Please try again.", vbExclamation
    Exit Sub
End If

TargetRow = Sheets("Codes").Range("D43").Value + 1

With Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 25)
    .Value = TimeValue(txtDepartTime)
    .NumberFormat = "hh:mm" 'departure time
End With

End Sub

(This solution assumes no one will intentionally enter 12am or 00:00 as the time)

Upvotes: 1

Related Questions