Hi all,
I posted this before Christmas but didn't receive any
answers so I thought I'd try again.

In the EDIT, REPLACE ... / OPTIONS screen, is a little
dropdown box that tells EXCEL to search the sheet or
workbook.

I run a macro that does a find / replace of text in the
workbook but the scope of the search is limited by this
dropdown. In other words, I cannot seem to over rule
this manual dropdown entry via code. Is there a VB
property that is associated with this drop down box?
I've looked really hard for this and have been unable to
locate it.

All the best and hoping for a solution,
Robert

Record a macro while you vary these options, and examine the generated code.

Jerry

Bony Pony wrote:

Hi all,
I posted this before Christmas but didn't receive any
answers so I thought I'd try again.

In the EDIT, REPLACE ... / OPTIONS screen, is a little
dropdown box that tells EXCEL to search the sheet or
workbook.

I run a macro that does a find / replace of text in the
workbook but the scope of the search is limited by this
dropdown. In other words, I cannot seem to over rule
this manual dropdown entry via code. Is there a VB
property that is associated with this drop down box?
I've looked really hard for this and have been unable to
locate it.

All the best and hoping for a solution,
Robert

Hi,
Thanks but like I said, I have done all the obvious
things.
Regards,
Robert
-----Original Message-----
Record a macro while you vary these options, and examine
the generated code.

Jerry

Bony Pony wrote:

Hi all,
I posted this before Christmas but didn't receive any
answers so I thought I'd try again.

In the EDIT, REPLACE ... / OPTIONS screen, is a little
dropdown box that tells EXCEL to search the sheet or
workbook.

I run a macro that does a find / replace of text in
the
workbook but the scope of the search is limited by
this
dropdown. In other words, I cannot seem to over rule
this manual dropdown entry via code. Is there a VB
property that is associated with this drop down box?
I've looked really hard for this and have been unable
to
locate it.

All the best and hoping for a solution,
Robert

.

Hi Robert

there is no VBA setting to change from current sheet to all sheets, you'll
need to cycle through all sheets in the workbook in your code if you want to
replace throughout the whole workbook., e.g.

Sub ReplWholeWB()
Dim Wks As Worksheet
For Each Wks In Worksheets
Wks.Cells.Replace What:="AAAAA", Replacement:="CCCCC", _
searchOrder:=xlByRows, MatchCase:=False
Next Wks
End Sub

---

Hope this helps
Cheers
JulieD



"Bony Pony" wrote in message
...
Hi,
Thanks but like I said, I have done all the obvious
things.
Regards,
Robert
-----Original Message-----
Record a macro while you vary these options, and examine
the generated code.

Jerry

Bony Pony wrote:

Hi all,
I posted this before Christmas but didn't receive any
answers so I thought I'd try again.

In the EDIT, REPLACE ... / OPTIONS screen, is a little
dropdown box that tells EXCEL to search the sheet or
workbook.

I run a macro that does a find / replace of text in
the
workbook but the scope of the search is limited by
this
dropdown. In other words, I cannot seem to over rule
this manual dropdown entry via code. Is there a VB
property that is associated with this drop down box?
I've looked really hard for this and have been unable
to
locate it.

All the best and hoping for a solution,
Robert

.

Hi Julie,
Thank you for your help. Dang!! You'd think that with
all of the useless properties in Excel VB, you think
they'd replicate useful full menu functionality. Ah
well ...

Thanks very much for confirming my own findings though.

Have a great day,
Robert
-----Original Message-----
Hi Robert

there is no VBA setting to change from current sheet to
all sheets, you'll
need to cycle through all sheets in the workbook in your
code if you want to
replace throughout the whole workbook., e.g.

Sub ReplWholeWB()
Dim Wks As Worksheet
For Each Wks In Worksheets
Wks.Cells.Replace What:="AAAAA", Replacement:="CCCCC",
_
searchOrder:=xlByRows, MatchCase:=False
Next Wks
End Sub

---

Hope this helps
Cheers
JulieD



"Bony Pony" wrote
in message
...
Hi,
Thanks but like I said, I have done all the obvious
things.
Regards,
Robert
-----Original Message-----
Record a macro while you vary these options, and
examine
the generated code.

Jerry

Bony Pony wrote:

Hi all,
I posted this before Christmas but didn't receive any
answers so I thought I'd try again.

In the EDIT, REPLACE ... / OPTIONS screen, is a
little
dropdown box that tells EXCEL to search the sheet or
workbook.

I run a macro that does a find / replace of text in
the
workbook but the scope of the search is limited by
this
dropdown. In other words, I cannot seem to over rule
this manual dropdown entry via code. Is there a VB
property that is associated with this drop down box?
I've looked really hard for this and have been unable
to
locate it.

All the best and hoping for a solution,
Robert

.



.

Hi Robert

i'm not overly surprised, AFAIK the functionality to search the whole
workbook was only introduced to the find / replace screen in ver 2002 - so
maybe in the next version they'll remember to "update" the VBA code.

Cheers
JulieD

"Bony Pony" wrote in message
...
Hi Julie,
Thank you for your help. Dang!! You'd think that with
all of the useless properties in Excel VB, you think
they'd replicate useful full menu functionality. Ah
well ...

Thanks very much for confirming my own findings though.

Have a great day,
Robert
-----Original Message-----
Hi Robert

there is no VBA setting to change from current sheet to
all sheets, you'll
need to cycle through all sheets in the workbook in your
code if you want to
replace throughout the whole workbook., e.g.

Sub ReplWholeWB()
Dim Wks As Worksheet
For Each Wks In Worksheets
Wks.Cells.Replace What:="AAAAA", Replacement:="CCCCC",
_
searchOrder:=xlByRows, MatchCase:=False
Next Wks
End Sub

---

Hope this helps
Cheers
JulieD



"Bony Pony" wrote
in message
...
Hi,
Thanks but like I said, I have done all the obvious
things.
Regards,
Robert
-----Original Message-----
Record a macro while you vary these options, and
examine
the generated code.

Jerry

Bony Pony wrote:

Hi all,
I posted this before Christmas but didn't receive any
answers so I thought I'd try again.

In the EDIT, REPLACE ... / OPTIONS screen, is a
little
dropdown box that tells EXCEL to search the sheet or
workbook.

I run a macro that does a find / replace of text in
the
workbook but the scope of the search is limited by
this
dropdown. In other words, I cannot seem to over rule
this manual dropdown entry via code. Is there a VB
property that is associated with this drop down box?
I've looked really hard for this and have been unable
to
locate it.

All the best and hoping for a solution,
Robert

.



.