how to get vba to display excel 2002+ "Find & Replace" dialog

[Majorstratdude]

Using VBA, one can display most if not all of Excel's builtin dialogs,
using code such as:

application,dialogs().show

The object browser shows the enum for the constants (such as
xlDialogFormulaReplace in the example above). However, I cant find
such a constant for the new single box "Find & Replace" dialog
introduced in Excel 2002 and beyond and described in KB288291. Any
ideas on how to display this puppy?

Major

 

[Peter T]

I don't have 2002 but this works in 2000

b = Application.Dialogs(xlDialogFormulaReplace).Show( _
"find_text", "replace_text")
' see other arg's in help, best to reset them

Regards,
Peter T

 

[Majorstratdude]

Peter T. -
This works in Excel 2003 as well, but in is not raising the new Single
Box Find & Replace dialog mentioned in the KB article I cited. Which I
need to use since 1) it has a "Find All" feature and 2) it is
modeless.
Thanks for the response, tho.

Major

 

[Majorstratdude]

I found a solution. Ironically, it was by looking at Peter T.'s
profile and earlier posts.

I found the command button that corresponded to the menu "Find"
function by enumerating the ID & Caption of the commandbar objects to
find the ID (1849) and then executed the cmd to raise the dialog.

Dim c As CommandBarButton

On Error Resume Next
Dim colCtrs As Collection

Set colctrls = CommandBars.FindControls
For Each c In colctrls
If InStr(1, c.Caption, "&Find") > 0 Then
Debug.Print c.ID & " : " & c.Caption & " : " & c.Tag & " : " &
c.Type & " : " & c.DescriptionText
End If
Next

I then used SENDKEYS to fill out and run the dialog itself. Not
pretty, but effective.

Set c = CommandBars.FindControl(ID:=1849) ' Find
c.Execute

With Application
.SendKeys ("ERR:{TAB}")
.SendKeys ("%t{TAB}")
.SendKeys ("&hw{TAB}")
.SendKeys ("%s{DOWN}{TAB}")
.SendKeys ("%LC{DOWN}{DOWN}{TAB}")
.SendKeys ("%i~~")
End With

Thanks for the inspiration.

Major

 

[Peter T]

Really, I don't recall posting anything quite like that but if it works I'll
take credit for it <g>

However think I would change -

Dim c As CommandBarButton

On Error Resume Next
Dim colCtrs As Collection

Set colctrls = CommandBars.FindControls

to
Dim c as Object 'CommandBarButton, CommandBarButton, CommandBarComboBox etc
dim colctrls as CommandbarConrols

Set colctrls = CommandBars.FindControls

or simply

for each c in CommandBars.FindControls

Regards,
Peter T