Open the Find & Replace Window Using a Macro

[sctrojangal]

I am creating a simple macro that just needs to select a certain range
of cells and then open the Replace & Find window to allow the user to
enter their Find & Replace criteria (it will change every time they
run the macro.

I found the code to do a replace if it was the same replace every
time, but cannot find a way to open the window...

Any advice would truly be appreciated!

 

[Dave Miller]

This should work for you:

Regards,
David Miller


Function FindAndReplace()
Dim MenuBar As CommandBar, _
Edit As CommandBarControls, _
Replace As CommandBarControl

On Error Resume Next
Set MenuBar = Application.CommandBars("Worksheet menu bar")
Set Edit = MenuBar.Controls.Item("Edit").Controls
Set Replace = Edit.Item("Replace...")

If Not Replace Is Nothing Then
Replace.Execute
End If

Set Replace = Nothing
Set Edit = Nothing
Set MenuBar = Nothing
End Function

 

[Guest]

assuming you didn't change the short cut key assignment, one way to do it is
to use SendKeys.


Application.SendKeys "^h"

 

[Guest]

Give this a try...

Application.Dialogs(xlDialogFormulaReplace).Show

 

[sctrojangal]

Thanks Jim,

That worked perfectly!

Give this a try...

Application.Dialogs(xlDialogFormulaReplace).Show
--
HTH...

Jim Thomlinson







- Show quoted text -

 

[Dave Miller]

Nice method Jim, I have never seen that before.

David Miller

 

[Guest]

All of the dialogs that you can access in XL can be shown via this type of
code. The list of dialogs is very long and worht a look. Eveything from Save
As and Print to the color pallete...