Results 1 to 7 of 7

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

This is a discussion on If ClipBoard Empty GoTo... Else Paste (How said in VBA?)? within the Excel Questions forums, part of the Question Forums category; Gentlemen If a line in VBA says: "ActiveSheet.Paste" and the ClipBoard is empty, the program crashes with a, [Run-time error ...

  1. #1
    Board Regular steve case's Avatar
    Join Date
    Apr 2002
    Posts
    722

    Default

    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 smozgur; Dec 30th, 2008 at 05:05 PM. Reason: changed referred thread URL with the actual forum link

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209

    Default

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


    On Error Resume Next
    ActiveSheet.Paste
    If Err Then MsgBox "Nothing to paste!": Err.Clear


    Kind Regards,
    Ivan F Moala From the City of Sails

  3. #3
    Board Regular steve case's Avatar
    Join Date
    Apr 2002
    Posts
    722

    Default

    Thank you

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

    The user is happy too

  4. #4
    New Member
    Join Date
    Jun 2011
    Posts
    23

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

    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

  5. #5
    New Member
    Join Date
    Jun 2011
    Posts
    23

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

    Anyone?

    Cheers

    Nick

  6. #6
    Board Regular Ruddles's Avatar
    Join Date
    Aug 2010
    Location
    Glevum Castra, Britannia
    Posts
    5,470

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

    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:
    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!
    R.
    ---
    On two occasions I have been asked, 'Pray, Mr Babbage, if you put into the machine wrong figures, will the right answers come out?'
    I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.

  7. #7
    New Member
    Join Date
    Jun 2011
    Posts
    23

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

    Quote Originally Posted by Ruddles View Post
    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:
    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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com