Reset Excel "Find and Replace" dialog box parameters

Question

How do I programmatically reset the Excel "Find and Replace" dialog box parameters to defaults ("Find what", "Replace with", "Within", "Search", "Look in", "Match case", "Match entire cell contents")?

I am using "Application.FindFormat.Clear" and "Application.ReplaceFormat.Clear" to reset find and replace cell formats.

Interestingly, after using "expression.Replace(FindWhat, ReplaceWhat, After, MatchCase, WholeWords)", the "FindWhat" string shows in the "Find and Replace" dialog box but not the "ReplaceWhat" parameter.

Answer 1

You can use the following command to open the "Replace" dialog with fields filled:

Application.Dialogs(xlDialogFormulaReplace).Show -arguments here-

the argument list is

find_text, replace_text, look_at, look_by, active_cell, match_case, match_byte

---

So far, the only way I've found to 'click' the buttons is with SendKey.

---

After much research and testing, I now know exactly what you want to do, but don't think it can be done (without SendKey). It appears that there is a bug in Excel, that won't reset the replacement value (from VBA), no matter what you try and set it to.

I did find this 'faster' way someone posted on MSDN, so you might give it a try.

Faster than Replace

Answer 2

You can use this macro to reset find & replace. Unfortunately, you have to call them both as there are one or two arguments unique to each, so if you want to reset everything, you're stuck. There is no 'reset', so the only way I have found is to execute a fake find & replace using the default parameters.

Sub ResetFind()
    Dim r As Range

    On Error Resume Next  'just in case there is no active cell
    Set r = ActiveCell
    On Error Goto 0

    Cells.Find what:="", _
               After:=ActiveCell, _
               LookIn:=xlFormulas, _
               LookAt:=xlPart, _
               SearchOrder:=xlByRows, _
               SearchDirection:=xlNext, _
               MatchCase:=False, _
               SearchFormat:=False
    Cells.Replace what:="", Replacement:="", ReplaceFormat:=False

    If Not r Is Nothing Then r.Select
    Set r = Nothing
End Sub