If ClipBoard Empty GoTo... Else Paste (How said in VBA?)?

steve case

Well-known Member
Joined
Apr 10, 2002
Messages
820
Gentlemen

If a line in VBA says: "ActiveSheet.Paste" and the ClipBoard is empty, the program crashes with a, [Run-time error '1004':
Paste method of Worksheet class failed]
Error message.

Does anyone know how to test for an empty ClipBoard in VBA?

By the Way, Maslan asked a similar question here:

Clipboard Oddities


Thnaks

StACase@mailcity.com
 
Last edited by a moderator:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Something like this may help....change
as required....

<pre/>
On Error Resume Next
ActiveSheet.Paste
If Err Then MsgBox "Nothing to paste!": Err.Clear
</pre>
 
Upvote 0
Thank you

Plugged that baby in, and it worked first time out of the box! WooHoo!

The user is happy too :)
 
Upvote 0
This code is exactly what I need but I'm fairly inexperienced with the use of Message Boxes so need some more help please.

This is my 'paste' code

Sub PasteData()
'
' PasteData Macro
'
Application.ScreenUpdating = False
Range("B1").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
Cells.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

When I try to paste with an empty clipboard the error described in the first post appears.

I tried adding the suggested code to my VBA but when I run the macro the following text just gets pasted into cell B1.

If Err Then MsgBox "Nothing to paste!": Err.Clear

Do I have to add a message box by some other means?

Thanks

Nick
 
Upvote 0
That's probably because you still had If Err Then MsgBox "Nothing to paste!": Err.Clear in the clipboard when you ran the macro, so it did exactly what you told it to do: paste the contents of the clipboard into B1.

Your code should look like this:-
Code:
[FONT=Courier New]Sub PasteData()[/FONT]
[FONT=Courier New]'[/FONT]
[FONT=Courier New]' PasteData Macro[/FONT]
[FONT=Courier New]'[/FONT]
[FONT=Courier New]  Application.ScreenUpdating = False[/FONT]
[FONT=Courier New]  Range("B1").Select[/FONT]
[FONT=Courier New][COLOR=red][B]  On Error Resume Next[/B][/COLOR][/FONT]
[FONT=Courier New]  ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= False[/FONT]
[FONT=Courier New][COLOR=red][B]  If Err Then MsgBox "Nothing to paste!": Err.Clear[/B][/COLOR][/FONT]
[FONT=Courier New]  Cells.Columns.AutoFit[/FONT]
[FONT=Courier New]  Application.ScreenUpdating = True[/FONT]
[FONT=Courier New]End Sub[/FONT]
To test the On Error clause, make sure your clipboard is empty and then run it: the message box will appear.

You've already tested it with the clipboard not empty!
 
Upvote 0
That's probably because you still had If Err Then MsgBox "Nothing to paste!": Err.Clear in the clipboard when you ran the macro, so it did exactly what you told it to do: paste the contents of the clipboard into B1.

Your code should look like this:-
Code:
[FONT=Courier New]Sub PasteData()[/FONT]
[FONT=Courier New]'[/FONT]
[FONT=Courier New]' PasteData Macro[/FONT]
[FONT=Courier New]'[/FONT]
[FONT=Courier New]  Application.ScreenUpdating = False[/FONT]
[FONT=Courier New]  Range("B1").Select[/FONT]
[FONT=Courier New][COLOR=red][B]  On Error Resume Next[/B][/COLOR][/FONT]
[FONT=Courier New]  ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= False[/FONT]
[FONT=Courier New][COLOR=red][B]  If Err Then MsgBox "Nothing to paste!": Err.Clear[/B][/COLOR][/FONT]
[FONT=Courier New]  Cells.Columns.AutoFit[/FONT]
[FONT=Courier New]  Application.ScreenUpdating = True[/FONT]
[FONT=Courier New]End Sub[/FONT]
To test the On Error clause, make sure your clipboard is empty and then run it: the message box will appear.

You've already tested it with the clipboard not empty!

D'oh.

Cheers dude.
 
Upvote 0
Code:
Private Declare PtrSafe Function CountClipboardFormats Lib "user32" () As Long

Function IsClipboardEmpty() As Boolean
    IsClipboardEmpty = (CountClipboardFormats() = 0)
End Function

Function IsClipboardEmpty2() As Boolean
IsClipboardEmpty2 = Application.CommandBars.FindControl(Id:=22).Enabled
'or:
'= (Application.ClipboardFormats(1) = -1) '??
End Function
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top