Extend code for all sheets - Modification checking

[Villalobos]

Hello,

Now I using the below mentioned code to check "what" "from" "to" "when" "on which sheet" the modifications/sheet. There are many sheet in my file and I not would like to place on every sheet this code. I would like to simplify... and my target is that only one code which is able to handle the all modifications on all sheets.

Public varPValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Errb As Integer
On Error GoTo ErrTrap:
If Target.Value <> varPValue Then
With Sheets("log").Cells(65000, 1).End(xlUp)
.Offset(1, 0).Value = Application.UserName
.Offset(1, 1).Value = "Modified cell:"
.Offset(1, 2).Value = Target.Address
.Offset(1, 3).Value = "Previous data:"
.Offset(1, 4).Value = varPValue
.Offset(1, 5).Value = "New data:"
.Offset(1, 6).Value = Target.Value
.Offset(1, 7).Value = "When:"
.Offset(1, 8).Value = Now()
.Offset(1, 9).Value = "Modified sheet"
.Offset(1, 10).Value = ActiveSheet.Name
End With
End If
Exit Sub
ErrTrap:
ErrNum = Err

If ErrNum = 13 Then
Resume Next
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
varPValue = Target.Value

End Sub

Can someone suggest anything how should I modify the code?
Thanks in advance!

 

[shrivallabha]

You can certainly copy the code in the sheets where you want this to happen but then if you have to make changes to it then you'll have to repeat the process across all sheet modules.

You should take a look at application events which come in ThisWorkbook module. They are pretty similar to what we use in Sheet modules but apply for complete workbook.

e.g. An equivalent event to

Private Sub Worksheet_Change(ByVal Target As Range)
'Code
End Sub

will be

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'You can add some sheet control code here to decide on which sheets to run this code etc.
'Code
End Sub

 

[Villalobos]

Hello Shirvallabha,

Thank you for your attention.
As I would like to run the macro my MSO 2013 shutdown.
Please, would you be so kind to look closer the code!?

 

[NARAYANK991]

Hi ,

Check the file here.

Note that you need to insert a module.

The existing code crashed Excel because it did not have the statements Application.EnableEvents = False before the code which changed worksheet cells and the statement Application.EnableEvents = True after.


Narayan

 

[Villalobos]

Hi Narayan,

Thank you for the explanation and the example!
That is clear now.

 

[SirJB7]

@Villalobos
Hi!
Not being a new member anymore, you're by your 70th post, so please post code properly as explained in the new members welcome forum. And displayed too at shrivallabha's signature. Yes, signatures are intended for being read too, they're not just decorative elements.
Regards!