pr1asd
03-30-2012, 03:14 AM
Hi All,
I am trying to get excel to track and record all changes made to a workbook. However, this does not work when a row or column is inserted.
Instead it shows Run Time Error '13': Type mismatch error.
I know that this is because the code is not set-up for tracking this kind of change and I was wondering if anyone can help me change it so that it will record this change.
Here is the code so far:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim NewVal As String
Dim oldVal As String
Dim lr As Long
If ActiveSheet.Name = "Changes" Then Exit Sub
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
oldVal = Target.Value
lr = Sheets("Changes").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Changes").Range("A" & lr) = Now
Sheets("Changes").Range("B" & lr) = ActiveSheet.Name
Sheets("Changes").Range("C" & lr) = Target.Address
Sheets("Changes").Range("D" & lr) = oldVal
Sheets("Changes").Range("E" & lr) = NewVal
Sheets("Changes").Range("F" & lr) = UNameWindows()
Target = NewVal
Application.EnableEvents = True
End Sub
I know what I would like for the end of the code:
lr = Sheets("Changes").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Changes").Range("A" & lr) = Now
Sheets("Changes").Range("B" & lr) = ActiveSheet.Name
Sheets("Changes").Range("C" & lr) = Target.Address
Sheets("Changes").Range("D" & lr) = "Row/Column inserted before "&
Sheets("Changes").Range("F" & lr) = UNameWindows()
But I am not sure how to get it to do this for an insertion or how to get it to record the row/column number/letter.
Please can someone help, this is driving me crazy?
Thanks
PR1ASD
P.S. the code used was from this website: forums.techguy.org/business-applications/810202-solved-track-changes-using-ms.html
I am trying to get excel to track and record all changes made to a workbook. However, this does not work when a row or column is inserted.
Instead it shows Run Time Error '13': Type mismatch error.
I know that this is because the code is not set-up for tracking this kind of change and I was wondering if anyone can help me change it so that it will record this change.
Here is the code so far:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim NewVal As String
Dim oldVal As String
Dim lr As Long
If ActiveSheet.Name = "Changes" Then Exit Sub
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
oldVal = Target.Value
lr = Sheets("Changes").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Changes").Range("A" & lr) = Now
Sheets("Changes").Range("B" & lr) = ActiveSheet.Name
Sheets("Changes").Range("C" & lr) = Target.Address
Sheets("Changes").Range("D" & lr) = oldVal
Sheets("Changes").Range("E" & lr) = NewVal
Sheets("Changes").Range("F" & lr) = UNameWindows()
Target = NewVal
Application.EnableEvents = True
End Sub
I know what I would like for the end of the code:
lr = Sheets("Changes").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Changes").Range("A" & lr) = Now
Sheets("Changes").Range("B" & lr) = ActiveSheet.Name
Sheets("Changes").Range("C" & lr) = Target.Address
Sheets("Changes").Range("D" & lr) = "Row/Column inserted before "&
Sheets("Changes").Range("F" & lr) = UNameWindows()
But I am not sure how to get it to do this for an insertion or how to get it to record the row/column number/letter.
Please can someone help, this is driving me crazy?
Thanks
PR1ASD
P.S. the code used was from this website: forums.techguy.org/business-applications/810202-solved-track-changes-using-ms.html