Trigger code if certian data is present

[Guest]

I have a worksheet that summarises a data sheet that is updated by the user
by copying a file created by another application.

Occassionally the data will contian "Normal" instead of "Norm" as a line
characteristic. I have a macro that will find "Normal" on the datasheet
("JobCard") and replae each instance with "Norm".

However, I have no guarantee that the user will remember to press the button
that runs this macro.

Is it possible for the datasheet ("JobCard") to be tested after being
updated (new Job Card pasted in) and any instance of "Normal" changed to
"Norm" before the user accesses the Summary sheet, or once each time the
Summary sheet is activated?

I have tried running the macro as a worksheet event, but get myself caught
up in circular process that I have to break out of.

Any suggestions greatly appreciated
Jim

 

[Roger Whitehead]

Would the Worksheet_Deactivate event suit your needs?

Try calling your search & replace from this event, so it fires when another
sheet is selected. Also call it from the Workbook_BeforeSave event? - In
which case the search & replace will have to refer explicitly to the JobCard
sheet.

HTH
Roger

 

[Guest]

Thanks Roger,
This looked promising at first but when I tried to select the "Summary"
sheet the error message, 'Can't find any data to replace...' would come up
and I can't then get to the "Summary" sheet from "JobCard".

This is the code.

Private Sub Worksheet_Deactivate()
Sheets("JobCard").Select
Selection.Replace What:="Normal", Replacement:="Norm", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Summary").Select
Range("B4").Select

End Sub

With or without 'Sheets("Summary").Select' causes the circular action and I
can't get to any other sheet because of the error message bring me back to
"JobCard".