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!

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

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


Application.SendKeys "^h"


--
Hope that helps.

Vergel Adriano


" wrote:

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!

Give this a try...

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

Jim Thomlinson


" wrote:

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!

Thanks Jim,

That worked perfectly!

On May 8, 8:38 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
Give this a try...

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

Jim Thomlinson



" wrote:
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!- Hide quoted text -

- Show quoted text -

Nice method Jim, I have never seen that before.

David Miller

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...
--
HTH...

Jim Thomlinson


"Dave Miller" wrote:

Nice method Jim, I have never seen that before.

David Miller