CODEWITHSUNDEEP
vbaexcel
Matt Ridge
Matt Ridge

Reputation: 3651

Make messages for specific Excel error Messages using VBA?

Is there a way to say if Error 1004 shows up, show message "Message" and If Error 9, show message "Message2" instead of generic non-descriptor geek speak message for end user?

Upvotes: 4

Views: 5990

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

What you are trying to do is called Error Handling.

See this Example. You can trap the Error Number using Err.Number

Sub Sample()
    On Error GoTo Whoa

    '~~> Rest of the code

    Exit Sub
Whoa:
    Select Case Err.Number
        Case 9
            MsgBox "Message1"
        Case 1004
            MsgBox "Message2"
    End Select
End Sub

FOLLOWUP

Sub Sample1()
    On Error GoTo Whoa

    '~~> Rest of the code

    Exit Sub
Whoa:
    MsgBox GetErrMsg(Err.Number)
End Sub

Sub Sample2()
    On Error GoTo Whoa

    '~~> Rest of the code

    Exit Sub
Whoa:
    MsgBox GetErrMsg(Err.Number)
End Sub

Function GetErrMsg(ErNo As Long) As String
    Select Case ErNo
        Case 9
            GetErrMsg = "Message1"
        Case 1004
            GetErrMsg = "Message2"
        Case Else
            GetErrMsg = "Message3"
    End Select
End Function

Upvotes: 7

Related Questions