VBA Check Clipboard Text Before Pasting

[jamesuk]

Hi All

Hoping someone can help me out, not sure if this is even possible!

I have a simple bit of code that pastes data from the clipboard into a worksheet:

Sub PasteWOc()




Dim dest As Range
    Set dest = Sheets("wo").Range("a1")
    
    dest.PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    


End Sub

Is it possible to have the code check the first 2 characters of text on the clipboard, and make sure it begins with 'WO' before pasting? if not, exit the sub?

Many thanks for your assistance in advance

 

[Eric W]

It is possible. First, go to the VBA Editor. In the menu, click on Tools --> References, then put a check next to Microsoft Forms 2.0 Object library. Then paste this code:

Sub PasteWOc()
Dim MyText As String

    Set MyData = New DataObject
    MyData.GetFromClipboard
    MyText = MyData.GetText

    If Left(MyText, 2) <> "WO" Then Exit Sub

    Sheets("wo").Range("a1") = MyText

End Sub

That should do it!

 

[jamesuk]

Brilliant! thanks Eric

can I ask, if someone else was to use the workbook on a different machine, would they too have to make reference to Object Lib for it to work?

 

[RoryA]

No, the reference is saved with the workbook. You can actually skip the reference and late bind the DataObject although the code is a little obscure:

   Set myData = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

rather than:

Set MyData = New DataObject

 

[jamesuk]

This worked perfectly! thank you!

one last question... I'm trying to force Excel to show a msge box and exit the sub if a debug error happens (when the clipboard is empty)

I've been playing with:

On Error GoTo ErrHandler:


ErrHandler:
 
 MsgBox "hello"
Exit Sub


Resume

but to no avail

any suggestions?
Many thanks!

 

[RoryA]

For example:

Sub PasteWOc()
    Dim MyText                As String
    Dim myData                As Object

    On Error GoTo ErrHandler
    Set myData = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    myData.GetFromClipboard
    MyText = myData.GetText

    If Left(MyText, 2) <> "WO" Then Exit Sub

    Sheets("wo").Range("a1") = MyText

    Exit Sub

ErrHandler:

    MsgBox "hello"

End Sub

 

[jamesuk]

Hi RoryA

Thanks! i'll give that a go and let you know how I get on

out of my own curiosity, that looks like a Regkey? can you explain to me what purpose that serves in the code?

 

[RoryA]

It's the CLSID for an MSForms.DataObject. It's the only way I know to late bind one.

 

[jamesuk]

Hi RoryA

That worked perfectly thank you for your help!

Always learn something new when I come here for help! :D