How to record who last modified the record in shared Microsoft Excel

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024

We all know Microsoft Excel as one of the most basic and first programs created for use with a graphical environment on computers.

Many times we have a shared Excel file that is being edited by multiple users. Maybe for some reason we need to audit and keep track of when each record was changed and by which user.

To do this we will need to collect the information from Active Directory via LDAP. Of course it is a requirement to use Active Directory in our network.

The example

Let's see how we will enable the change and user fields to be filled automatically.

First we need to enable Developer mode if we haven't already.

In Excel – File – Options – Customize Ribbon – click the Developer box.

View Code.

We see the VBA management window has opened. There we will insert the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Set WSHnet = CreateObject("WScript.Network")
    username = WSHnet.username
    UserDomain = WSHnet.UserDomain
    Set objUser = GetObject("WinNT://" & UserDomain & "/" & username & ",user")
    UserFullName = objUser.FullName
Dim sUserName As String
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xTimeColumn = 7  'βάζουμε τον αριθμό της κολόνας που θα εμφανίζεται η ώρα μεταβολής
xNColumn = 8  'βάζουμε τον αριθμό της κολόνας που θα εμφανίζεται το όνομα του Χρήστη
xRow = Target.Row
xCol = Target.Column
sUserName = Environ$("username")

If Target.Text <> "" Then
    Select Case xCol
    Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
        On Error Resume Next
        Set xDPRg = Target.Dependents
        For Each xRg In xDPRg
            If xRg.Column = xCellColumn Then
                'Worksheets("Sheet1").Unprotect "password" 'unlock cells, αν έχουμε βάλει κωδικό για προστασία του αρχείου
                Cells(xRow, xTimeColumn) = Now()
                'Cells(xRow, xNColumn) = sUserName         'username
                Cells(xRow, xNColumn) = UserFullName     'FullName
                'Worksheets("Sheet1").Protect "password"   'lock cells, αν έχουμε βάλει κωδικό για προστασία του αρχείου
            End If
        Next
    End Select
End If

In the field xTimeColumn put the number of the column where the change time will appear in the field as well xNColumn we put the number of the column where the user's name will be displayed.

After placing the code on the sheet, select save.

The result

Now we will see by going to tease some field for example the "Total Price" from 80 to 90 the time and the name that the user has declared in the Active Directory.