I have a spreadsheet that tracks the time various files are sent. For each file I have an input box that asks for the time the file sent, and there are a few other options that can be put in that input box.
If the person does not enter a valid entry, I would like to send them back to the input box so they can try again.
This is my code, which works for the first error, but if someone enters the wrong value a second time, they just get the standard 'Type Mismatch' error. I am creating this for people who don't have any knowledge of VBA and would like to make it as easy as possible.
There are multiple instances of the following code, each for a different file time. Thanks in advance!
CFP:
If Err Then MsgBox Error & " Invalid value. Please enter only 'blank', 'holiday', 'skip' or time value."
Err.Clear
tm = UCase(Application.InputBox("What time was the CFP file sent?"))
On Error GoTo CFP
If tm = "BLANK" Then
ActiveCell.Value = "Blank"
ElseIf tm = "HOLIDAY" Then
ActiveCell.Value = "Holiday"
ElseIf tm = "SKIP" Then
ActiveCell.Value = "Skipped"
Else
ActiveCell.Value = TimeValue(tm)
End If
Error handlers go below the procedure they're handling errors for:
Public Sub DoSomething
On Error GoTo ErrHandler
'code here
Cleanup:
'cleanup code here
Exit Sub
ErrHandler:
'error-handling code here
Resume Cleanup
End Sub
What's happening is that once the first error is raised, because the error handler label is at the top, instead of jumping down into the error handler, the code jumps up back to the top, and VBA understands the rest of the procedure to be the error-handling subroutine... which never returns to the actual procedure body, because it is the procedure's body.
You're using the wrong control flow structure for the job, see @Matteo's answer for what you need.