VBA Script Help

steve2115

Board Regular
Joined
Mar 17, 2014
Messages
82
Need help with the below vb script. The script works however I need it to also "track" the changes on fields that contain formulas. I have a 'Timeline' sheet that has a column titled 'Task End Date' which has formula (=IF(E8="Enter Weeks","",IFERROR((D8+(E8*7)),"")) in it. The 'Change Log' script does not log the changes on this field because it is a calculated field. I require the 'Change Log' to capture the changes. The trigger to start capturing the changes on the 'Change Log' should be IF cell M3="YES"on sheet A3. If the cell M3=NO ,I do not require any changes to be logged.
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 If ActiveSheet.Name = "Change Log" Then Exit Sub
 Application.EnableEvents = False
 NewVal = Target.Value
 Application.Undo
 oldVal = Target.Value
 UserName = Environ("Username")
 lr = Sheets("Change Log").Range("A" & Rows.Count).End(xlUp).Row + 1
 Sheets("Change Log").Range("A" & lr) = Now
 Sheets("Change Log").Range("B" & lr) = ActiveSheet.Name
 Sheets("Change Log").Range("C" & lr) = Target.Address
 Sheets("Change Log").Range("D" & lr) = oldVal
 Sheets("Change Log").Range("E" & lr) = NewVal
 Sheets("Change Log").Range("F" & lr) = UserName
 Target = NewVal
 Application.EnableEvents = True
 End Sub
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,704
Messages
5,833,221
Members
430,197
Latest member
edeibold

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