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

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" <(E-Mail Removed)> wrote in message
news:6A64F01F-A804-4EAB-834D-(E-Mail Removed)...
> 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

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:6A64F01F-A804-4EAB-834D-(E-Mail Removed)...
> > 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
>
>
>

> 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


"pnp" <(E-Mail Removed)> wrote in message
news:56852B0F-638B-4CCD-A49D-(E-Mail Removed)...
> 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" wrote:
>
> > 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" <(E-Mail Removed)> wrote in message
> > news:6A64F01F-A804-4EAB-834D-(E-Mail Removed)...
> > > 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
> >
> >
> >

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

"pnp" <(E-Mail Removed)> wrote in message
news:6A64F01F-A804-4EAB-834D-(E-Mail Removed)...
> 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

> 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

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" wrote:

> 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
>
> "pnp" <(E-Mail Removed)> wrote in message
> news:6A64F01F-A804-4EAB-834D-(E-Mail Removed)...
> > 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,
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

"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
>
> > 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
>
>