VBA Script Help

steve2115

Board Regular
Joined
Mar 17, 2014
Messages
79
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Watch MrExcel Video

Forum statistics

Threads
1,108,502
Messages
5,523,296
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top