Error handling and give message

[Linda Lo]

Dear all,

I want to have a massage instead of Debug message to the user when running time error 9 . When running error, message was shown instead of Debug box. In the other side, message was also shown in normal run. What is missing in my Marco? Please help me to point out? Much Thanks.

Linda Lo



Sub myMacro()

Dim str As String

On Error GoTo myError

Sheets("str").Activate

myError:
MsgBox "Date of record was incorrect for the file, please replace it!"


End Sub

 

[rlv01]

You must have an Exit Sub statement so the macro does not display the error message when there is no error.

Sub myMacro()
  
    Dim str As String

    On Error GoTo myError
    Sheets("str").Activate
    On Error GoTo 0
  
    Exit Sub

myError:
    If Err.Number = 9 Then
        MsgBox "Date of record was incorrect for the file, please replace it!"
    Else
        MsgBox "Run-time Error '" & Err.Number & "':" & vbCrLf & Err.Description, vbOKOnly Or vbExclamation, "Error"
    End If
End Sub

(Tip: For future posts , please try to use code tags like I did above when posting code. It makes your code easier to read and copy.

https://www.mrexcel.com/board/help/how-to-post-your-vba-code

)

 

[Linda Lo]

Thanks a lot!