record any changes by macro

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
259
greeting to all of you

i'm new to macro, and i found below code which can record any changes in excel and that's just prefect for my workbook
however i have almost 20 macros to run, so i'm wondering is there any possible way to record after that?

VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim LR As Long
Dim c As Range
Application.EnableEvents = False
With Sheets("LogSheet")
    For Each c In Target
    LR = .Cells(Rows.count, "A").End(xlUp).Row + 1
    .Cells(LR, "A").Value = Sh.Name & "!" & c.Address
    .Cells(LR, "B").Value = Now
    .Cells(LR, "C").Value = c.Value
    Next c
End With
Application.EnableEvents = True
End Sub

stay safe everyone
thanks again
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,702
If you put the
VBA Code:
Application.EnableEvents = False
at the beginning of your other macros (or if they're always run in a sequence just the first one should be enough) that turns off the events off while they're running.

The macro you posted the code of is one of the events macros, triggered by Sheet Change.

Events macros are usually located in the Sheets or Workbook modules instead of the normal modules and the name of the macro always tells you - or at least gives you a hint - of how they're triggered.

Just make sure you turn the events back on at the end of your other macros as well so that the events macros will be triggered when needed.
 

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
259
If you put the
VBA Code:
Application.EnableEvents = False
at the beginning of your other macros (or if they're always run in a sequence just the first one should be enough) that turns off the events off while they're running.

The macro you posted the code of is one of the events macros, triggered by Sheet Change.

Events macros are usually located in the Sheets or Workbook modules instead of the normal modules and the name of the macro always tells you - or at least gives you a hint - of how they're triggered.

Just make sure you turn the events back on at the end of your other macros as well so that the events macros will be triggered when needed.
thanks Misca for your briefly answered

i have a command button with few macros inside, should i put this code in between my macros like:
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim LR As Long
Dim c As Range
Application.EnableEvents = False
Module9.Macro001
Module9.Macro002
Module9.Macro003
Module8.Macro999
With Sheets("LogSheet")
    For Each c In Target
    LR = .Cells(Rows.count, "A").End(xlUp).Row + 1
    .Cells(LR, "A").Value = Sh.Name & "!" & c.Address
    .Cells(LR, "B").Value = Now
    .Cells(LR, "C").Value = c.Value
    Next c
End With
Application.EnableEvents = True
End Sub
 

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,702
Your current code runs all the other macros every time the sheet is changed while the events are on.

I'd recommend adding the EnableEvents lines to the beginning & end of the code that runs from the command button and leave the original event macro as it was originally.

Unless, of course, you want to run the other macros as well whenever the sheet is changed, that is.
 

Forum statistics

Threads
1,136,787
Messages
5,677,729
Members
419,716
Latest member
MPunt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top