Programmatically call the Find and Replace window?

[plh]

Hi All,
I am looking for a way to program the Find and Replace window. What I want to do
is set up a hot key to call the window with the contents of the selected cell in
the "Find What?" space. (Ordinarily I would just record a macro & alter what I
got but that recording feature does not seem to record anything when the Find
and Replace sub-window is used.)
Thank You,
-plh

 

[Rick Rothstein]

You can use Application.Dialogs(xlDialogFormulaReplace).Show to show the
dialog box and you can list the arguments afterwards to control the various
fields and other options. In your case, you would want something like
this...

Application.Dialogs(xlDialogFormulaReplace).Show ActiveCell.Value

which will fill the "Find what" field with the contents of the active cell.
You can also specify what to replace it with by specifying a second text
like so...

Application.Dialogs(xlDialogFormulaReplace).Show _
ActiveCell.Value, "Replacement Text"

Note that I deliberately used a line continuation character to force the
text wrap so that your newsreader wouldn't text wrap it at an odd location.

The arguments you can specify (in this order) are: find_text, replace_text,
look_at, look_by, active_cell, match_case, match_byte

You can get the proper values to use for all of them (except for the
active_cell argument which I can find no documentation for) by looking up
the Find Method in the VBA help files. Here is a more complete example
showing one of the many possible combination of arguments (note that I
omitted the active_cell argument)

 

[Mio]

Would this possibly help.

Sub CallFRWindow()
Dim FindPhrase As String
Dim ReplacePhrase As String
FindPhrase = ActiveCell.Value
ReplacePhrase = ""
Application.Dialogs(xlDialogFormulaReplace).Show FindPhrase,
ReplacePhrase
End Sub


Regards,

Mika Oukka

 

[plh]

Thanks, that's nice, but I need a little more, and you seem like you would know.
I notice when I do this I don't get the "Find All" button. That is what I would
want. Ideally it would have the sub-window at the bottom already opened with the
results list.
I looked in the sources you mentioned and did not find out how to include that
button. I am doing this for people who are not so computer savvy & so seem to
have a problem doing it the way I do which is to just go Ctrl-C then Ctrl-F then
Ctrl-V
Thanx,
-plh

 

[Mio]

Sound like I would be better to through the tedious task of teaching
everyone how to use the "Find Next"- and "Replace"-button.

BR,

Mika Oukka