If ClipBoard Empty GoTo... Else Paste (How said in VBA?)?
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

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

  1. #1
    Board Regular steve case's Avatar
    Join Date
    Apr 2002
    Posts
    764
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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 06: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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    764
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,754
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

  8. #8
    New Member
    Join Date
    Nov 2013
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

      
    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

User Tag List

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