Paste to Find dialog box using a macro

  • Thread starter Thread starter danno-c
  • Start date Start date
D

danno-c

Manually I can paste a value into the “Find what:†field of the find dialog
box (Ctrl+F, Ctrl+V). However, when I attempt to record this into a macro,
the macro will not record it.

Am I missing something?

Thanks, for any help you can offer.
Dan
 
Option Explicit
Sub testme()

Dim MyData As DataObject
Dim myStr As String

Set MyData = New DataObject

myStr = "" 'or whatever you want if there's no text in the clipboard

On Error Resume Next
MyData.GetFromClipboard
myStr = MyData.GetText(1)
On Error GoTo 0

'maybe this, too???
myStr = Replace(myStr, vbCrLf, " ")
myStr = Replace(myStr, vbCr, " ")
myStr = Replace(myStr, vbLf, " ")

Application.Dialogs(xlDialogFormulaFind).Show arg1:=myStr

End Sub

Chip Pearson has some notes that you'll want to read.
http://www.cpearson.com/excel/clipboard.htm

Especially the note about using tools|references and checking "Microsoft Forms
2.0 object library").
 
I got a compile error from the Dim MyData As DataObject line. I'm looking
through the help files now to see what i can do. I'm sure this is just a
newbie problem on my end.
 
Back
Top