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

[steve case]

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

[Ivan F Moala]

Something like this may help....change
as required....

On Error Resume Next

ActiveSheet.Paste

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

[steve case]

Thank you

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

The user is happy too

[Barquois]

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

[Barquois]

Anyone?

Cheers

Nick

[Ruddles]

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:-

Sub PasteData()
'
' PasteData Macro
'
  Application.ScreenUpdating = False
  Range("B1").Select
  On Error Resume Next
  ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= False
  If Err Then MsgBox "Nothing to paste!": Err.Clear
  Cells.Columns.AutoFit
  Application.ScreenUpdating = True
End Sub

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!

[Barquois]

Originally Posted by Ruddles

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:-

Sub PasteData()
'
' PasteData Macro
'
  Application.ScreenUpdating = False
  Range("B1").Select
  On Error Resume Next
  ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= False
  If Err Then MsgBox "Nothing to paste!": Err.Clear
  Cells.Columns.AutoFit
  Application.ScreenUpdating = True
End Sub

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.