Neodubois
08-21-2008, 03:17 AM
Hi
I would like to have a xls sheet which would track changes done in another sheet with details and the user if possible
thanks
Bob Phillips
08-21-2008, 04:46 AM
Multiple workbooks or just one? All worksheets?
Neodubois
08-21-2008, 05:23 AM
well first for just one workbook but prob for several and for sure for all worksheets
if not possible just 1 workbook and 1 sheet would already be a good thing
Bob Phillips
08-21-2008, 06:47 AM
Not a problem, just basic details.
Option Explicit
Public WithEvents App As Application
Private NextRow As Long
Const SHEET_NAME As String = "Tracking"
Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Sh.Parent Is Me Then
Application.EnableEvents = False
With Me.Worksheets(SHEET_NAME)
.Cells(NextRow, "A").Value = Target.Address(, , , True)
.Cells(NextRow, "B").Value = Target.Text
.Cells(NextRow, "C").Value = Environ("Username")
.Cells(NextRow, "D").Value = Now
.Cells(NextRow, "D").NumberFormat = "dd mmm yyyy hh:mm:ss"
.Columns("A:C").AutoFit
NextRow = NextRow + 1
End With
Application.EnableEvents = True
End If
End Sub
Private Sub Workbook_Open()
Dim Sh As Worksheet
Set App = Application
On Error Resume Next
Set Sh = Me.Worksheets(SHEET_NAME)
On Error GoTo 0
If Sh Is Nothing Then
Set Sh = Me.Worksheets.Add(after:=Me.Worksheets(Me.Worksheets.Count))
Sh.Name = SHEET_NAME
End If
With Sh
If .Range("A1").Value = "" Then
Sh.Range("A1").Value = "Changed"
Sh.Range("B1").Value = "Value"
Sh.Range("C1").Value = "User"
Sh.Range("D1").Value = "Timestamp"
Sh.Range("A1:D1").Font.Bold = True
NextRow = 2
Else
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End If
End With
End Sub
This is workbook event code, and goes in the tracker workbook.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.