Open the Find & Replace Window Using a Macro
 

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!

Open the Find & Replace Window Using a Macro
 

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

Open the Find & Replace Window Using a Macro
 

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!

Open the Find & Replace Window Using a Macro
 

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!

Open the Find & Replace Window Using a Macro
 

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 -

Open the Find & Replace Window Using a Macro
 

Nice method Jim, I have never seen that before.

David Miller

Open the Find & Replace Window Using a Macro
 

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