How to create a hidden audit trail in Excel workbook... 1

[petrosky]

Hi,

I have looked at the FAQs pages and have got some nifty code that will return the worksheet name and the NT logon name.

I need to somehow have a hidden sheet that updates with the user name and date/time of last save of a file.

The file is a price list so it will help me identify which user has made which changes historically.

I hope someone can help point me in the right direction.

Regards,

Peter

Remember- It's nice to be important,
but it's important to be nice

 

[xlbo]

Have a hidden sheet called Audit
Then, use the BEFORE SAVE event (double click "workbook" in VBE and choose before save from the right side dropdown)

Then add this code to it

Dim aSht as worksheet
set aSht = sheets("Audit&quot
application.screenupdating = false
mUser = environ("username&quot
with aSht
.visible = true
set fCell = .columns("A&quot.find(mUser,lookin:=xlvalues,lookat:=xlwhole)
If not fCell is nothing then
'user already exists in audit
fCell.offset(0,1).value = now()
else
'user not in audit sheet
lRow = .range("A65536&quot.end(xlup).row+1)
.range("A" & lRow).value = mUser
.range("B" & lRow).end(xlup).row+1).value = now()
end if
.hidden = true
end with

This will create a 2 column audit trail with userID and date/time of last save by the user

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[petrosky]

Hi Geoff,

Many thanks and a star from me.

With some minor changes your code worked fine in XL97.

Here is the final code if any one is interested.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim aSht As Worksheet

Set aSht = Sheets("Audit&quot
Application.ScreenUpdating = False
muser = ReturnUserName()

With aSht
.Visible = True
Set fCell = .Columns("A&quot.Find(muser, LookIn:=xlValues, lookat:=xlWhole)
If Not fCell Is Nothing Then
'user already exists in audit
fCell.Offset(0, 1).Value = Now()
Else
'user not in audit sheet
lRow = .Range("A65536&quot.End(xlUp).Row + 1
MsgBox muser

.Range("A" & lRow).Value = muser
.Range("B" & lRow).Value = Now()
End If
.Visible = xlSheetHidden
End With

End Sub

The function ReturnUserName is this.

Function ReturnUserName() As String
' returns the NT Domain User Name
Dim rString As String * 255, sLen As Long, tString As String
tString = ""
On Error Resume Next
sLen = GetUserName(rString, 255)
sLen = InStr(1, rString, Chr(0))
If sLen > 0 Then
tString = Left(rString, sLen - 1)
Else
tString = rString
End If
On Error GoTo 0
ReturnUserName = UCase(Trim(tString))
End Function


Thanks again Geoff.

Peter

Remember- It's nice to be important,
but it's important to be nice

 

[xlbo]

Does Environ("username&quot not work for getting the userID for you then ??

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[petrosky]

Hi again,

I am using 97, not sure if this is the difference.
Yesterday afternoon, I almost did my head in trying to add to your code the actual worksheet name the user saved/worked on.

I once again used your nifty code on each page to identify the sheet name.

=MID(CELL("FILENAME",F10),FIND("]",CELL("FILENAME",F10))+1,255)

but could not find a way to "capture" the data and drop it into column C.

I tried to copy/paste the value as below with no luck
.Range("c" & lrow).Value = Selection.Paste


Any ideas?

Regards,

Peter

Remember- It's nice to be important,
but it's important to be nice

 

[KenWright]

Hi Geoff, only a thought, but given that the user could potentially disable macros, you could always have the sheet that the user needs to work with as xlveryhidden to start with and then have the code unhide it on fileopen. If the user had disabled the macros he then couldn't get to the data and therefore would be unable to bypass the audit sheet routine.

Regards
Ken...........

 

[xlbo]

To add the sheet name:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim aSht As Worksheet,wSht as worksheet
set wSht = activesheet
Set aSht = Sheets("Audit&quot
Application.ScreenUpdating = False
muser = ReturnUserName()

With aSht
.Visible = True
Set fCell = .Columns("A&quot.Find(muser, LookIn:=xlValues, lookat:=xlWhole)
If Not fCell Is Nothing Then
'user already exists in audit
fCell.Offset(0, 1).Value = Now()
fCell.Offset(0, 2).value = wSht.name
Else
'user not in audit sheet
lRow = .Range("A65536&quot.End(xlUp).Row + 1
MsgBox muser

.Range("A" & lRow).Value = muser
.Range("B" & lRow).Value = Now()
.range("C" & lRow).Value = wSht.name
End If
.Visible = xlSheetHidden
End With

End Sub

And Ken, yes, that is my standard routine for combatting disabled macros

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[petrosky]

Geoff,

Great code!

Thanks again.

Peter

Remember- It's nice to be important,
but it's important to be nice