You need to use error trapping, similar to the following code. Replace [whatever] with the actual error code number of the error you want a custom message for, and [your custom error message] with the actual message you want displayed.
On Error Goto Error_Label
'code that may generate error goes here
Done_Label:
Exit Sub
Error Label:
If Err.Number = [whatever] Then
Msgbox [your custom error message]
Else
Msgbox Err.Description
End If
Goto Done_Label