xld,
Yes, I have this built into the code already which makes the change.
If ActiveSheet.Range("A100").Value = 1 Then Exit Sub
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Set copySheet = Worksheets("Update Room")
Set pasteSheet = Worksheets("Log")
copySheet.Unprotect
Range("A100").Value = 1
copySheet.Range("F3").Copy
pasteSheet.Unprotect
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
copySheet.Select
Range("A1").Select
copySheet.protect
and what I currently have in the log sheet is this :
Private Sub Worksheet_Activate()
Me.protect
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rChange As Range
Set rChange = Intersect(Target, Range("A:A"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
rCell.Offset(0, 1).Value = UserName()
rCell.Offset(0, 2).Value = Date & " " & Time()
Else
rCell.Offset(0, 1).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
Public Function UserName()
UserName = Environ$("UserName")
End Function
Private Sub Worksheet_Deactivate()
Me.Unprotect
End Sub
So..I don't know anymore..