About a year ago(!) someone on this group supplied me
with the text for a macro that enabled Excel 2000 to
search for text across all the sheets of a workbook
(Excel XP can do this anyway).

The trouble is that if you have lots of occurences of the
find text, the "hit any key" message keeps coming up in a
loop and the only way to get out of it is to press CTRL-
BREAK and crash the macro. Excel counts "Escape" as any
key and just keeps on with the loop.

Can anyone suggest a command I could use so that Escape
just exits the macro? (OnEscape(EndSub) or something?)

The macro is below. Thanks for any help anyone can give.

Steve Wylie


Sub FindAll()
Dim sh As Worksheet
Dim rng As Range
Dim sStr As String
sStr = InputBox("Enter search text")
If sStr = "" Then
MsgBox "You hit cancel"
Exit Sub
End If
For Each sh In ActiveWorkbook.Worksheets
Set rng = sh.Cells.Find(What:=sStr, _
After:=sh.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
firstAddress = rng.Address
Do
If Not rng Is Nothing Then
Application.Goto rng, True
MsgBox "Hit key to continue"
End If
Set rng = sh.Cells.FindNext(rng)
Loop Until rng.Address = firstAddress
End If
Next
End Sub