vba 重置 Excel“查找和替换”对话框参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/243368/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Reset Excel "Find and Replace" dialog box parameters
提问by DaveParillo
How do I programmatically reset the Excel Find and Replacedialog box parameters to defaults ("Find what", "Replace with", "Within", "Search", "Look in", "Match case", "Match entire cell contents")?
如何以编程方式将 ExcelFind and Replace对话框参数重置为默认值（“查找内容”、“替换为”、“范围内”、“搜索”、“查找范围”、“匹配案例”、“匹配整个单元格内容”）？
I am using Application.FindFormat.Clearand Application.ReplaceFormat.Clearto reset find and replace cell formats.
我正在使用Application.FindFormat.Clear和Application.ReplaceFormat.Clear重置查找和替换单元格格式。
Interestingly, after using expression.Replace(FindWhat, ReplaceWhat, After, MatchCase, WholeWords), the FindWhatstring shows in the Find and Replacedialog box but not the ReplaceWhatparameter.
有趣的是，使用 后expression.Replace(FindWhat, ReplaceWhat, After, MatchCase, WholeWords)，FindWhat字符串显示在Find and Replace对话框中，但不显示ReplaceWhat参数。
回答by DaveParillo
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
回答by Lance Roberts
You can use the following command to open the "Replace" dialog with fields filled:
您可以使用以下命令打开填充了字段的“替换”对话框：
Application.Dialogs(xlDialogFormulaReplace).Show -arguments here-
Application.Dialogs(xlDialogFormulaReplace).Show -arguments here-
the argument list is
参数列表是
find_text, replace_text, look_at, look_by, active_cell, match_case, match_byte
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.
到目前为止，我发现“单击”按钮的唯一方法是使用 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.
经过大量研究和测试，我现在确切地知道您想要做什么，但不要认为它可以完成（没有 SendKey）。Excel 中似乎存在一个错误，无论您尝试将其设置为什么，都不会重置替换值（来自 VBA）。
I did find this 'faster' way someone posted on MSDN, so you might give it a try.
我确实发现有人在 MSDN 上发布了这种“更快”的方式，因此您可以尝试一下。
回答by Mike
No need to use sendkeys you can easily refer to the values you need to reset the dialog box values.
无需使用发送键，您可以轻松引用重置对话框值所需的值。
Sub ResetFindReplace()
'Resets the find/replace dialog box options
Dim r As Range
On Error Resume Next
Set r = Cells.Find(What:="", _
LookIn:=xlFormulas, _
SearchOrder:=xlRows, _
LookAt:=xlPart, _
MatchCase:=False)
On Error GoTo 0
'Reset the defaults
On Error Resume Next
Set r = Cells.Find(What:="", _
LookIn:=xlFormulas, _
SearchOrder:=xlRows, _
LookAt:=xlPart, _
MatchCase:=False)
On Error GoTo 0
End Sub
回答by dave
I tested this and it works. I have borrowed parts from a few places
我测试了这个并且它有效。我从几个地方借过零件
Sub RR0() 'Replace Reset & Open dialog (specs: clear settings, search columns, match case)
'Dim r As RANGE 'not seem to need
'Set r = ActiveCell 'not seem to need
On Error Resume Next 'just in case there is no active cell
On Error GoTo 0
Application.FindFormat.Clear 'yes
Application.ReplaceFormat.Clear 'yes
Cells.find what:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext
Cells.Replace what:="", Replacement:="", ReplaceFormat:=False, MatchCase:=True 'format not seem to do anything
'Cells.Replace what:="", Replacement:="", ReplaceFormat:=False 'orig, wo matchcase not work unless put here - in replace
'If Not r Is Nothing Then r.Select 'not seem to need
'Set r = Nothing
'settings choices:
'match entire cell: LookAt:=xlWhole, or: LookAt:=xlPart,
'column or row: SearchOrder:=xlByColumns, or: SearchOrder:=xlByRows,
Application.CommandBars("Edit").Controls("Replace...").Execute 'YES WORKS
'Application.CommandBars("Edit").Controls("Find...").Execute 'YES same, easier to manipulate
'Application.CommandBars.FindControl(ID:=1849).Execute 'YES full find dialog
'PROBLEM: how to expand options?
'SendKeys ("%{T}") 'alt-T works the first time, want options to stay open
Application.EnableEvents = True 'EVENTS
End Sub
