Anyone know of a way to search across all sheets in a workbook? Is there a
way to add this functionality to the normal Find feature?

This is a good discussion on some issues with find and replace across many or
single sheets.
http://www.vbaexpress.com/forum/showthread.php?t=11444
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"JoeSpareBedroom" wrote:

Anyone know of a way to search across all sheets in a workbook? Is there a
way to add this functionality to the normal Find feature?

Thanks, John. One more question, so I don't waste time looking for something
that can't be done: Using VBA, is there a way to trap for CTRL-F, stop it
from invoking the normal Find function, and redirect it to a different
routine, assuming I find one in the discussion you pointed me to? I don't
need details - just a yes or no is enough.


"John Bundy" (remove) wrote in message
...
This is a good discussion on some issues with find and replace across many
or
single sheets.
http://www.vbaexpress.com/forum/showthread.php?t=11444
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"JoeSpareBedroom" wrote:

Anyone know of a way to search across all sheets in a workbook? Is there
a
way to add this functionality to the normal Find feature?

That functionality is built-in after Excel 97

EditFindOptionsWithin. Enable "Workbook"


Gord Dibben MS Excel MVP

On Wed, 07 May 2008 13:31:17 GMT, "JoeSpareBedroom"
wrote:

Anyone know of a way to search across all sheets in a workbook? Is there a
way to add this functionality to the normal Find feature?

That's easy... For example, to re-assign CTRL-F to a subroutine named MyFind
while that workbook is open, add this code in the ThisWorkbook module:

Private Sub Workbook_Activate()
'Assign macro shortcut = {Ctrl}f
Application.OnKey "^f", "MyFind"
End Sub

Private Sub Workbook_Deactivate()
'Clear macro shortcut = {Ctrl}f
Application.OnKey "^f", ""
End Sub

However, CTRL-F is disabled for any other workbooks that are open at the
same time as the workbook with this code. Relaunching Excel will restore the
normal CTRL-F functionality.

If you're new to macros, this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/200...e-elses-macro/

Regarding your original question (I didn't read John's link yet), the
regular Find/Replace dialog provides at least two ways to search and/or
replace on multiple sheets. You can click the Options button, then change the
'Within' option to workbook instead of worksheet. The Find/Replace operation
will include all sheets in the workbook. Or, you can select multiple sheets
to search those sheets in one operation.

Hope this helps,

Hutch

"JoeSpareBedroom" wrote:

Thanks, John. One more question, so I don't waste time looking for something
that can't be done: Using VBA, is there a way to trap for CTRL-F, stop it
from invoking the normal Find function, and redirect it to a different
routine, assuming I find one in the discussion you pointed me to? I don't
need details - just a yes or no is enough.


"John Bundy" (remove) wrote in message
...
This is a good discussion on some issues with find and replace across many
or
single sheets.
http://www.vbaexpress.com/forum/showthread.php?t=11444
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"JoeSpareBedroom" wrote:

Anyone know of a way to search across all sheets in a workbook? Is there
a
way to add this functionality to the normal Find feature?

I'm using Excel 2000, and I see no Options thing available to me as you
described it. My only choices a

Search by: Rows or Columns
Look In: Formulas, Values or Comments

Two check boxes: Match Case & Find Entire Cells Only

That's all I have.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
That functionality is built-in after Excel 97

EditFindOptionsWithin. Enable "Workbook"


Gord Dibben MS Excel MVP

On Wed, 07 May 2008 13:31:17 GMT, "JoeSpareBedroom"

wrote:

Anyone know of a way to search across all sheets in a workbook? Is there a
way to add this functionality to the normal Find feature?

Tom, the CTRL-F subroutine is helpful, but the Options button doesn't exist
in Excel 2000.


"Tom Hutchins" wrote in message
...
That's easy... For example, to re-assign CTRL-F to a subroutine named
MyFind
while that workbook is open, add this code in the ThisWorkbook module:

Private Sub Workbook_Activate()
'Assign macro shortcut = {Ctrl}f
Application.OnKey "^f", "MyFind"
End Sub

Private Sub Workbook_Deactivate()
'Clear macro shortcut = {Ctrl}f
Application.OnKey "^f", ""
End Sub

However, CTRL-F is disabled for any other workbooks that are open at the
same time as the workbook with this code. Relaunching Excel will restore
the
normal CTRL-F functionality.

If you're new to macros, this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/200...e-elses-macro/

Regarding your original question (I didn't read John's link yet), the
regular Find/Replace dialog provides at least two ways to search and/or
replace on multiple sheets. You can click the Options button, then change
the
'Within' option to workbook instead of worksheet. The Find/Replace
operation
will include all sheets in the workbook. Or, you can select multiple
sheets
to search those sheets in one operation.

Hope this helps,

Hutch

"JoeSpareBedroom" wrote:

Thanks, John. One more question, so I don't waste time looking for
something
that can't be done: Using VBA, is there a way to trap for CTRL-F, stop it
from invoking the normal Find function, and redirect it to a different
routine, assuming I find one in the discussion you pointed me to? I don't
need details - just a yes or no is enough.


"John Bundy" (remove) wrote in message
...
This is a good discussion on some issues with find and replace across
many
or
single sheets.
http://www.vbaexpress.com/forum/showthread.php?t=11444
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"JoeSpareBedroom" wrote:

Anyone know of a way to search across all sheets in a workbook? Is
there
a
way to add this functionality to the normal Find feature?

You can use Jan Karel Pieterse's FlexFind:
http://www.oaltd.co.uk/MVP/

JoeSpareBedroom wrote:

Anyone know of a way to search across all sheets in a workbook? Is there a
way to add this functionality to the normal Find feature?

--

Dave Peterson

Have you tried grouping the sheets and do your EditFind?

Excel 97 would replace across grouped sheets but not find.

Don't know about 2000..........never used that version but I thought it had the
feature. Guess not.


Gord

On Wed, 07 May 2008 20:13:07 GMT, "JoeSpareBedroom"
wrote:

I'm using Excel 2000, and I see no Options thing available to me as you
described it. My only choices a

Search by: Rows or Columns
Look In: Formulas, Values or Comments

Two check boxes: Match Case & Find Entire Cells Only

That's all I have.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
That functionality is built-in after Excel 97

EditFindOptionsWithin. Enable "Workbook"


Gord Dibben MS Excel MVP

On Wed, 07 May 2008 13:31:17 GMT, "JoeSpareBedroom"

wrote:

Anyone know of a way to search across all sheets in a workbook? Is there a
way to add this functionality to the normal Find feature?

Grouping works. There's an interesting clue to work with.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Have you tried grouping the sheets and do your EditFind?

Excel 97 would replace across grouped sheets but not find.

Don't know about 2000..........never used that version but I thought it
had the
feature. Guess not.


Gord

On Wed, 07 May 2008 20:13:07 GMT, "JoeSpareBedroom"

wrote:

I'm using Excel 2000, and I see no Options thing available to me as you
described it. My only choices a

Search by: Rows or Columns
Look In: Formulas, Values or Comments

Two check boxes: Match Case & Find Entire Cells Only

That's all I have.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
That functionality is built-in after Excel 97

EditFindOptionsWithin. Enable "Workbook"


Gord Dibben MS Excel MVP

On Wed, 07 May 2008 13:31:17 GMT, "JoeSpareBedroom"

wrote:

Anyone know of a way to search across all sheets in a workbook? Is there
a
way to add this functionality to the normal Find feature?