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?
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 LambertFabrice LambertCommented:
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.
0
FloraAuthor Commented:
I have another worksheet called Logs

i want to store all changes there. this code did not work.
0
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
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Fabrice LambertFabrice LambertCommented:
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

2

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
0
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!
0
FloraAuthor Commented:
Fabrice,

that is an excellent master piece
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.