Audit trail macro on excel?

[joieroxx]

I have a workbook that is accessed by several users on ONE stand-alone computer. I need a way to track changes made on this book - basically the who, what, when, how it was changed. The sheets are hidden unless a valid username and password are entered on my "main" sheet which is of course visible. I am using the following macro to achieve this:


[VBA] Private Sub CommandButton2_Click()
Dim strUser As String, strPword As String, strWs As String
strUser = Me.Username.Value
strPword = Me.Password.Value

Select Case strUser
Case "user1"
If strPword = "password1" Then
Sheets(1).Visible = xlSheetVisible
Sheets(2).Visible = xlSheetVisible
Sheets(3).Visible = xlSheetVisible
Sheets(4).Visible = xlSheetVisible
Sheets(5).Visible = xlSheetVisible
Unload Me
End If
Case "user2"
If strPword = "password2" Then
Sheets(1).Visible = xlSheetVisible
Sheets(2).Visible = xlSheetVisible
Sheets(3).Visible = xlSheetVisible
Sheets(4).Visible = xlSheetVisible
Sheets(5).Visible = xlSheetVisible
Unload Me
End If
Case "user3"
If strPword = "password3" Then
Sheets(1).Visible = xlSheetVisible
Sheets(2).Visible = xlSheetVisible
Sheets(3).Visible = xlSheetVisible
Sheets(4).Visible = xlSheetVisible
Sheets(5).Visible = xlSheetVisible
Unload Me
End If
Case Else
MsgBox "Incorrect passwword or user name", vbCritical + vbOKOnly, "Timewriting"
End Select
End Sub

Private Sub CommandButton1_Click()
Unload Me
ActiveWorkbook.Close
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "You must enter your user name & password to access sheet", vbCritical + vbOKOnly, "Password required"
End If
End Sub[/VBA]



-------------
Ok so that works, but now...how can I get a log of that user who last accessed the workbook with the date and time and what changes were made??? I'd like for these changes to be automatically recorded on an "very hidden" sheet everytime it is saved. Is this at all possible?

Suggestions are welcome. I know this would be easier with MS Access, but my colleagues are more comfortable with Excel; they find it easier.

Thank you!!
JM

Aussiebear: Edited to enable the code to be placed within the vba tags

[mdmackillop]

Hi JM
Welcome to VBAX.
The simplest way would be to save copies of the file using SaveCopyAs to a new location, with the User, Date and Time saved in it.

[figment]

joieroxx:

i personaly would use the method described by mdmackillop, but if you cant,then try this;

It requires you to make a new sheet, but you can always leave that sheet hidden. Also if you ever managed to compleatly fill the log sheet, this code will bomb, but being that your only using two cells evertime you log something. It shoule take you a while.

[vba]
Private Sub CommandButton2_Click()
Dim strUser As String, strPword As String, strWs As String
Dim usercorrect As Boolean

usercorrect = True
strUser = Me.UserName.Value
strPword = Me.Password.Value

Select Case strUser
Case "user1"
If strPword = "password1" Then
Sheets(1).Visible = xlSheetVisible
Sheets(2).Visible = xlSheetVisible
Sheets(3).Visible = xlSheetVisible
Sheets(4).Visible = xlSheetVisible
Sheets(5).Visible = xlSheetVisible
Unload Me
End If
Case "user2"
If strPword = "password2" Then
Sheets(1).Visible = xlSheetVisible
Sheets(2).Visible = xlSheetVisible
Sheets(3).Visible = xlSheetVisible
Sheets(4).Visible = xlSheetVisible
Sheets(5).Visible = xlSheetVisible
Unload Me
End If
Case "user3"
If strPword = "password3" Then
Sheets(1).Visible = xlSheetVisible
Sheets(2).Visible = xlSheetVisible
Sheets(3).Visible = xlSheetVisible
Sheets(4).Visible = xlSheetVisible
Sheets(5).Visible = xlSheetVisible
Unload Me
End If
Case Else
usercorrect = False
MsgBox "Incorrect passwword or user name", vbCritical + vbOKOnly, "Timewriting"
End Select
If usercorrect Then Call loguser(strUser)
End Sub

Function loguser(user As String)
Dim notloged As Boolean
Dim col As Long
notloged = True
col = 1
With Sheets(1)
While notloged
If Cells(65536, col).End(xlUp).Row <> 1 Or Cells(1, col) = "" Then
.Cells(.Cells(65536, col).End(xlUp).Row + 1, col + 1) = Format(Now(), "MM/DD/YYYY ttttt")
.Cells(.Cells(65536, col).End(xlUp).Row + 1, col) = user
.Cells(1, col) = "USER"
.Cells(1, col + 1) = "Time of log in"
notloged = False
Else
col = col + 2
End If
Wend
End With
End Function

Private Sub CommandButton1_Click()
Unload Me
ActiveWorkbook.Close
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "You must enter your user name & password to access sheet", vbCritical + vbOKOnly, "Password required"
End If
End Sub
[/vba]

[Brandtrock]

You could check this out.

[tstav]

Might I offer another alternative for logging users' actions (I can see in Ozgrid they
offer a text file solution, too (Brandtrock's link)) .
I use a .log file (could also be a .txt file) which is created automatically in a
specified folder (without the users knowing of course).
A text file has no row-number limit.
In the 'logApplication' I log the path of the application, because some users copy the
original App to a local folder, and I would like to trace that as well.
I have already used this in quite a number of multi-user VBA applications and it hasn't failed so far.

Regards, tstav

[vba]'This goes in the standard code module
Public Type Record
logUserName As String * 6
logBlank1 As String * 1
logComputerName As String * 12
logBlank2 As String * 1
logAction As String * 15
logBlank3 As String * 1
logApplication As String * 70
logBlank4 As String * 1
logDate As String * 10
logBlank5 As String * 1
logTime As String * 8
logCrLf As String * 2
End Type
Public Constr strLogPath as String = "C:\Myfolder" '<-- Supply your own
'''''''''''''''''''''''''''''''''''''''
'This goes whereever I need to create a log entry, be it Open/Close the file or whatever.
PutLog Rec, "Open" 'Opened the file
PutLog Rec, "Close" 'Closed the file
PutLog Rec, "Show SheetX" 'Activated a Sheet
PutLog Rec, "Hide SheetX" 'Made a Sheet invisible, etc
'''''''''''''''''''''''''''''''''''''''
'This goes in the standard code module
Sub PutLog(ByRef Rec As Record, ByVal strAction As String)
Dim intFile As Integer
On Error GoTo ErrorHandler
intFile = FreeFile(1)
Open strLogPath & "\" & "ThisLog.log" For Random As #intFile Len = Len(Rec)
Do While Not EOF(intFile) 'Search for first empty row (=practically always the last row)
Get #intFile, , Rec
If Asc(Rec.logUserName) = 0 Then 'found empty row in log
With Rec
.logUserName = Environ("UserName") '<-- Supply whatever suits you
.logBlank1 = " "
.logComputerName = Environ("ComputerName") '<-- Supply whatever suits you
.logBlank2 = " "
.logAction = strAction
.logBlank3 = " "
.logApplication = ThisWorkbook.Path & "\" & ThisWorkbook.Name
.logBlank4 = " "
.logDate = Format(CStr(Date), "dd/mm/yyyy")
.logBlank5 = " "
.logTime = Format(CStr(Time), "hh:mm:ss")
.logCrLf = vbCrLf
End With
Put #intFile, , Rec
Exit Do
End If
Loop
ErrorHandler:
If Err Then Err.Clear
Close #intFile
End Sub[/vba]

Edit: I just edited some comments

[joieroxx]

Thank you everyone for your assistance. I will try your suggetions and let you know how it all works out.

Thank you once again!!
JM

[Neodubois]

Indeed thanks

it is also possible to know whether other files take information (copy or whatever) from the original one ?

ow and in which file do I put this code ? obviously not the one we want to monitor users could see it ?

And also in my case the file to monitor is on a share drive