How to add messagebox in case of Error 1004

Question

I want to improve whole code by adding MsgBox in case of:

Run-time error '1004': PasteSpecial method of Range class failed.

This Error is caused if clipboard is empty and I run the macro. Any Advise?

Sub Test()
On Err.Number = 1004 GoTo ErrMsg
Dim Val As Variant
Sheets("Sheet 3").Select
Val = Range("A2").Value
Sheets("Sheet 1").Select
Call TextFromClipboard
Range("AY" & Val).Select
ActiveSheet.Paste
Sheets("Sheet 3").Select
ErrMsg:
MsgBox "Nothing to paste!", vbCritical, "Clipboard is empty!"
End Sub

Orgin

I always find it better to truly trap an error if it can be trapped, rather than relying on GoTo. See [this answer](https://stackoverflow.com/questions/35595258/how-to-check-if-clipboard-is-empty-of-text) for more. — Scott Holtzman, Mar 04 '20 at 13:12

score 7 · Accepted Answer · answered Mar 04 '20 at 13:16

I always find it better to truly trap an error if it can be trapped, rather than relying on GoTo.

Based on this answer you can create a function to see if the clipboard is empty.

Function clipboardEmpty() as Boolean
'requires reference to Microsoft Forms 2.0 Object Library

    Dim myDataObject As DataObject
    Set myDataObject = New DataObject

    myDataObject.GetFromClipboard

    clipboardEmpty = Not myDataObject.GetFormat(1) = True 

End Sub

You can then update your procedure to this:

If clipboardEmpty Then
    Msgbox "No Data to Paste!"
Else
   'Do Stuff
End If

I just add link for others who don't know how to enable reference to the Microsoft Forms 2.0 Object Library link](https://stackoverflow.com/questions/35610429/why-do-i-not-see-the-microsoft-forms-2-0-object-library) — Andrew, Mar 04 '20 at 13:44

Warcupine · Answer 2 · 2020-03-04T13:09:32.540

2

Use a select case in the error handler to handle the error number not in the goto statement.

You could probably remove the need all together for that particular error by removing the selects and not using the clipboard.

Sub Test()
    On Error GoTo ErrMsg
    Dim Val As Variant
    Sheets("Sheet 3").Select
    Val = Range("A2").Value
    Sheets("Sheet 1").Select
    Call TextFromClipboard
    Range("AY" & Val).Select
    ActiveSheet.Paste
    Sheets("Sheet 3").Select
Exit Sub
ErrMsg:
    Select Case Err.Number
    Case 1004
        MsgBox "Nothing to paste!", vbCritical, "Clipboard is empty!"
    End Select
End Sub

edited Mar 04 '20 at 13:09

answered Mar 04 '20 at 13:04

Warcupine

4,460
3
15
24

It doesn't matter with this code, but I consider it good practice to put an `Exit Sub` just before the `ErrMsg` line. You don't want to fall into the error handler unless there is truly an error. – Brian M Stafford Mar 04 '20 at 13:08

How to add messagebox in case of Error 1004

2 Answers2