Replace in a sheet and not the entire workbook

[ammx]

Hi,

I'm having problems with the RANGE.REPLACE method when being called in a subroutine.
I have written some VBA code that works fine only when the last FIND/REPLACE action (via the EDIT- FIND/REPLACE menu option) scanned just the active 'SHEET'. However, the same code fails when the last search or replace action scanned the entire WORKBOOK.

Using the macro recorder was not of any help either. There is no difference in the recorded code for SHEET or WORKBOOK scans.
I scanned various forums already for this issue, but couldn't find anything that helped me either.

How can I tell the RANGE.REPLACE method to just use the specified range ("A7") on a specified worksheet (sh)?

I thought the syntax should be:

sh.Range("a7").Replace ...

Attached is a small worksheet that isolates the problem. To try it,
search for something on a single sheet, then press the 3 buttons on sheets 1-3. Now search something in the entire workbook and press the 3 buttons again. You will now see some red error messages...

Please help me,

Michael

P.S.

I use Excel 2002 (10.6823.6817) SP3, multilingual
Doesn't work in English nor in German...

Here is the code:

Public Sub fixThisSheet()
    Dim ActualCalcMethod    As Variant
    On Error GoTo exit_sub
    ActualCalcMethod = Application.Calculation
    Application.DisplayStatusBar = True
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Call FixFormulas(ActiveSheet)
    exit_sub:
    Application.StatusBar = "Recalculating all sheets...please wait!"
    Application.Calculation = ActualCalcMethod
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.StatusBar = False
End Sub

Public Sub FixFormulas(sh As Worksheet)
    ' only works well when the last search was by sheet and not by workbook!!!
    ' by sheet   : only the 'TOTAL:' in the actual sheet is replace by workbook: all occurrences of 'TOTAL:' are replaced
    sh.Range("a7").Replace _
    What:="TOTAL:", Replacement:="## ERROR ##", LookAt:=xlPart, SearchOrder:=xlByRows, _
    MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    ' restore 'TOTAL:' on actual sheet only
    sh.Range("a7") = "TOTAL:"
End Sub