Get text from clipboard using GetText - avoid error on empty clipboard

Handle the errors with On Error GoTo as shown here:

Sub GetClipBoardText()   Dim DataObj As MSForms.DataObject   Set DataObj = New MsForms.DataObject '<~~ Amended as per jp's suggestion   On Error GoTo Whoa   '~~> Get data from the clipboard.   DataObj.GetFromClipboard   '~~> Get clipboard contents   myString = DataObj.GetText(1)   MsgBox myString   Exit SubWhoa:   If Err <> 0 Then MsgBox "Data on clipboard is not text or is empty"End Sub

You will notice that it will handle an empty clipboard as well.

NB: to make the code work, you must have a reference to "Microsoft Forms 2.0 Object Library" (this file can be found at C:\windows\system32\FM20.dll on 32-bit machines, or at C:\Windows\sysWOW64\FM20.dll on 64-bit machines), otherwise you'd get the error "User-Defined type not defined".

You can empty the clipboard before testing the above code by using the code below. Please paste it in a module.

Private Declare Function OpenClipboard Lib "User32.dll" _(ByVal hWndNewOwner As Long) As Long  Private Declare Function EmptyClipboard Lib "User32.dll" () As Long Private Declare Function CloseClipboard Lib "User32.dll" () As Long Public Sub ClearClipboard()    Dim Ret      Ret = OpenClipboard(0&)    If Ret <> 0 Then Ret = EmptyClipboard    CloseClipboardEnd Sub

EDIT: you may also determine if the clipboard is empty by using this code:

Private Declare Function CountClipboardFormats Lib "user32" () As LongSub Sample()    If (CountClipboardFormats() = 0) = True Then        MsgBox "Clipboard is empty"    Else        MsgBox "Clipboard is not empty"    End IfEnd Sub

add the follwoing code just b4 the breaking line for debuging.... the error disapeared for me after that test.. wierd but it somehow works (Excel 2010)

myString = DataObj.GetText(1)MsgBox myString