Paste to Find dialog box using a macro
 

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

Paste to Find dialog box using a macro
 

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").

danno-c wrote:

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

--

Dave Peterson

Paste to Find dialog box using a macro
 

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.



"Dave Peterson" wrote:

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").

danno-c wrote:

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

--

Dave Peterson

Paste to Find dialog box using a macro
 

Found it.
It's all good now.
Great link.

Thanks,
Dan

"danno-c" wrote:

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.



"Dave Peterson" wrote:

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").

danno-c wrote:

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

--

Dave Peterson