CODEWITHSUNDEEP
excelvba
Andrew
Andrew

Reputation: 25

How to add messagebox in case of Error 1004

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

Upvotes: 2

Views: 855

Answers (2)

Scott Holtzman
Scott Holtzman

Reputation: 27249

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

Upvotes: 7

Warcupine
Warcupine

Reputation: 4640

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

Upvotes: 2

Related Questions