asked on

How can i trace any changes made in my mastersheet via VBA?

I need help with tracking any changes made by users on a file shared in a network drive.

i want to be able to see in the log sheet who changed what cell and when?

i tried googling but could not find anything useful.

thanks.

Fabrice Lambert

Hi,

Use the workbook's sheetchange event:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim user As String
    
    user = Environ("USERNAME")
    Debug.Print Now & vbTab & user & vbTab & "Worksheet: " & Sh.Name & vbTab & "target: " & Target.Address & vbtab & "Value: " Target.Formula
End Sub

Open in new window

Note: you'll probably want something more elaborated than a debug.print for logging.

Flora Edwards

ASKER

I have another worksheet called Logs

i want to store all changes there. this code did not work.

Kamlesh Rao

If you have SharePoint in your environment, then it can handle the versioning automatically. The users can easily check-out and check-in files.

I would personally prefer to build a custom database/application to handle such scenarios. I am not sure if this is feasible in your scenario.

-KR

ASKER CERTIFIED SOLUTION

Fabrice Lambert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rgonzo1971