Make messages for specific Excel error messages using VBA?

Question

Is there a way to tell if error message 1004 appears, show the message "Message" and "If error 9", show the message "Message2" instead of the general message about an imperfect geek call for the end user?

Answer 1

What you are trying to do is called error handling.

See this example. You can block 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