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.
LVL 6
FloraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertConsultingCommented:
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.
FloraAuthor Commented:
I have another worksheet called Logs

i want to store all changes there. this code did not work.
Kamlesh RaoIT ConsultantCommented:
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
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Fabrice LambertConsultingCommented:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim rng As Excel.Range
    Dim data(5) As String
    
        '// don't trigger for the "log" worksheet
    If (Sh.Name <> "Log") Then
        Set wb = ThisWorkbook
            '// get the "log" worksheet
        Set ws = ThisWorkbook.Worksheets("Log")
            '// get the last row
        Set rng = ws.Range(ws.Cells(ws.UsedRange.Rows.Count, 1), ws.Cells(ws.UsedRange.Rows.Count, 5))
        If (rng.Cells(1).Value <> vbNullString) Then
            Set rng = rng.Offset(rowoffset:=1)
        End If
            '// gather data
        data(LBound(data)) = Now                        '// time
        data(LBound(data) + 1) = Environ("USERNAME")    '// user
        data(LBound(data) + 2) = Sh.Name                '// worksheet name
        data(LBound(data) + 3) = Target.Address         '// range address
        data(LBound(data) + 4) = Target.Formula         '// range value
            '// paste data in the log worksheet
        rng.Value = data
            '// cleanup
        Set rng = Nothing
        Set ws = Nothing
        Set wb = Nothing
    End If
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rgonzo1971Commented:
Hi,

pls try in the worksheet MAster module
Private Sub Worksheet_Change(ByVal Target As Range)
    user = Environ("USERNAME")
    arr = Array(Now, user, Target.Address(0, 0), Target.Formula)
    Sheets("Logs").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 4) = arr
End Sub

Open in new window

Regards
Kamlesh RaoIT ConsultantCommented:
I tried Fabrice Lambert code at my end and its producing the desired results. This is cool!

But if you think from Security aspect, then anyone can go and tamper the VBA code or even delete the Log sheet.  Please check your security needs and then go ahead with this solution!
FloraAuthor Commented:
Fabrice,

that is an excellent master piece
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.