Microsoft Office Tutorials and References
In Depth Information
Logging Actions When a Cell Changes
'Open the file¶
File = FreeFile¶
Open FilePath For Append As #File¶
Length = LOF(File)¶
If Length = 0 Then¶
'Put some headers¶
Write #File, "Workbook", "Worksheet", "Range", "Value", _¶
"Formula", "Username", "Date"¶
End If¶
'Loop through the cells¶
For Each Cell In Target.Cells¶
With Cell¶
Workbook = .Worksheet.Parent.FullName¶
Worksheet = .Worksheet.Name¶
Address = .Address(RowAbsolute:=False, _¶
ColumnAbsolute:=False)¶
Value = .Value¶
If .HasFormula Then¶
Formula = .Formula¶
Else¶
Formula = ""¶
End If¶
Username = GetWinUserName()¶
TimeStamp = Now()¶
End With¶
'Write the information¶
Write #File, Workbook, Worksheet, Address, Value, Formula, _¶
Username, TimeStamp¶
Next Cell¶
Close #File¶
End Sub¶
' * * * * *¶
Function GetWinUserName()¶
'Variable declaration¶
Dim strUserName As String¶
strUserName = String(255, Chr$(0))¶
GetUserName strUserName, 255¶
strUserName = Left$(strUserName, InStr(strUserName, _¶
Chr$(0)) - 1)¶
GetWinUserName = strUserName¶
End Function¶
Exl
View the Appendix to learn how to store this procedure
in a ThisWorkbook module.
Option Explicit¶
' * * * * *¶
Private Sub Workbook_SheetChange(ByVal Sh As Object, _¶
ByVal Target As Range)¶
If TypeName(Sh) = "Worksheet" Then¶
Search JabSto ::




Custom Search