How to record who last modified the record in shared Microsoft Excel
Certifications:
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.
