Change
MsgBox "Hit any key to continue"
to
If MsgBox ("Continue?", vbokcancel) = vbcancel then exit sub
Robert Flanagan
Macro Systems
Delaware, U.S.
http://www.add-ins.com
Productivity add-ins and downloadable books on
VB macros for Excel
Signature
"Steve Wylie" wrote in message
...
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