Excel Bugs

D

Dakcenturi

Joined
Jan 11, 2010
Messages
4
Reaction score
0
Hi all, been working on a simple worksheet to track changes and sign off/lock sheets. It seems that there is some sort of problem though as the signoff/locking macro locks the tracking sheet. Not sure where the problem is occurring.

Here is the macro I am using on one sheet to signoff and lock the sheet:

Code: 
Sub Sign_Off_PDT()
Worksheets("PDT").Cells(5, 6) = Worksheets("Instruction Sheet").Cells(5, 2)
Worksheets("PDT").Cells(6, 6) = Now()
ActiveSheet.Protect Password:="sysop"
End Sub

Here is the tracking I am using:

Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If ActiveSheet.Name = "TrackChanges_Record" Then Exit Sub

Application.EnableEvents = False

UserName = Environ("USERNAME")

NewVal = Target.Value

Application.Undo

oldVal = Target.Value

lr = Sheets("TrackChanges_Record").Range("A" & Rows.Count).End(xlUp).Row + 1

Sheets("TrackChanges_Record").Range("A" & lr) = Now
Sheets("TrackChanges_Record").Range("B" & lr) = ActiveSheet.Name
Sheets("TrackChanges_Record").Range("C" & lr) = Target.Address
Sheets("TrackChanges_Record").Range("D" & lr) = oldVal
Sheets("TrackChanges_Record").Range("E" & lr) = NewVal
Sheets("TrackChanges_Record").Range("F" & lr) = UserName

Target = NewVal

Application.EnableEvents = True
End Sub

I've attached the document. Also I am using excel 2003.
 

Attachments

Is everything in the right place? I mean I put the tracking code on the whole workbook and then the signoff macro on the button but it's only on the one page. Why is it effecting the tracking page?
 
Back
Top