Same code in several sheets..
 

Hi All,

I have following code repeated in Four sheets ina
workbbok containg ten sheets. and some other code in
another four sheets

Is there a way that I can avoid this repetation?

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo doTHIS
Application.EnableEvents = False
If Target.Address = "$D$4" Then
FindMatch
End If
doTHIS:
Application.EnableEvents = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)

If Target.Address = "$C$10" Then

Set rng = Range("C4:C8")
On Error GoTo Dotry
Application.EnableEvents = 0
If Application.CountA(rng) < 5 Then
MsgBox "Enter All Records!", vbOKOnly, _
"Invalid Record found"
ActiveSheet.Unprotect Password:="adfm"
rng.SpecialCells(xlBlanks)(1).Select
ActiveSheet.Protect Password:="adfm"
ActiveSheet.EnableSelection = xlUnlockedCells
Exit Sub
Else
DoIt
End If
End If
Dotry:
Application.EnableEvents = True
End Sub

TIA
Soniya

Same code in several sheets..
 

Hi Soniya

I the ThisWorkbook module you have these events:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

Put your code there, and decide what to do from Sh.Name or Sh.Index.
--
HTH. Best wishes Harald
Excel MVP
Followup to newsgroup only please

"Soniya" skrev i melding
...
Hi All,

I have following code repeated in Four sheets ina
workbbok containg ten sheets. and some other code in
another four sheets

Is there a way that I can avoid this repetation?

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo doTHIS
Application.EnableEvents = False
If Target.Address = "$D$4" Then
FindMatch
End If
doTHIS:
Application.EnableEvents = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)

If Target.Address = "$C$10" Then

Set rng = Range("C4:C8")
On Error GoTo Dotry
Application.EnableEvents = 0
If Application.CountA(rng) < 5 Then
MsgBox "Enter All Records!", vbOKOnly, _
"Invalid Record found"
ActiveSheet.Unprotect Password:="adfm"
rng.SpecialCells(xlBlanks)(1).Select
ActiveSheet.Protect Password:="adfm"
ActiveSheet.EnableSelection = xlUnlockedCells
Exit Sub
Else
DoIt
End If
End If
Dotry:
Application.EnableEvents = True
End Sub

TIA
Soniya