Control search/replace dialog startup values

[pnp]

Hi all

Is there any way to programmatically interact with the controls on the
search/replace dialog (xlDialogSearch). Especially interested in setting the
match case and match entire cell checkboxes, and set the find and replace
words.

I've searched the help for "dialog box arguments" which comes up with a long
list of parameters to the show method for different dialogs, but....
xlDialogSearch is not on that list...

Can anyone help me with this?

best regards
/Anders

 

[Peter T]

Hi Anders,

What is 'xlDialogSearch'

Try the Find or Replace method, this is what the macro recorder gave me with
Replace

Cells.Replace What:="aaa", _
Replacement:="bbb", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

See Find & Replace in help

Regards,
Peter T

 

[pnp]

Hi Peter

The xlDialogSearch is an enum value used with Application.Dialogs to get an
dialog object.

dlg = Application.Dialogs(xlDialogSearch)

The above gives me the search dialog object which i then can show to popup
the dialog. I have a search/replace macro which can replace the text with the
..replace method. But I want an option to replace interactive, which should
display the dialog as you would with normal search/replace, the values for
match case, and keywords should just be filled out.

And the arguments for the Dialog.Show method is just named arg1, arg2 ..,
arg30 and I don't know which to use with what values.

/Anders

 

[Peter T]

The xlDialogSearch is an enum value used with Application.Dialogs to get
an

dialog object.

xlDialogSearch is not a defined constant in any of my VBA libraries, does it
exit for you?
What do you get if you type
?xlDialogSearch
in the Immediate window (ctrl-g) and hit Enter, or if you search same in
Object browser (F2)

Regards,
Peter T

 

[NickHK]

Using :
Application.Dialogs(xlDialogSearch).Show
in XL2002 I get the side bar similar to that for the Office Clipboard and
New Workbook, rather than a standard XL dialog. As I find this annoying, I
don't use it.
This is more to do with searching for text/files on the system than
find/replace in the Excel sheet.

As such, this is not the dialog you need and Peter has shown you code.
AFAIK, the dialog retains the settings from the last time it was used,
although default back when Excel is closed.

NickHK

 

[Peter T]

Using :
Application.Dialogs(xlDialogSearch).Show
in XL2002 I get the side bar ...

Ah, XL2002 or later and not in my XL2000.
I would have stated my version but didn't know new Dialogs had been added in
later versions.

Regards,
Peter T

 

[pnp]

Sorry... My bad...

I should have tested that before i panicked over not being able to find the
parameters... hmmm...

After a little more investigation I found out that the enum value I want is
xlDialogFormulaReplace. The arguments for this is specified in the dialog box
arguments list.

Thanks for helping out - it sometimes take another man to point out obvious
mistakes...

/Anders

 

[NickHK]

Peter,
A quick test between XL2K and XLXP shows a few additional dialogs.
I'll leave it as an exercise for the reader to determine which these are
<g>.

723 724 731 732 753 754 755 771 773

NickHK