We work in a networked environment and there are only
2 users who have access to our Excel file. These 2 users
never work the same shift, so therefore only one user
will be modifying this file at any time.

My question is, is there any VBA code to determine who
was the last user to modify/save an Excel 2007 file??
Sometimes I would like to see who was the last user to
edit this file.

Thank you!

i usually just create a hidden sheet. then in the thisworkbook before close
module, add
worksheets("Hidden Sheet name").range("A1").value =environ("Username")

--


Gary Keramidas
Excel 2003


"Robert Crandal" wrote in message
...
We work in a networked environment and there are only
2 users who have access to our Excel file. These 2 users
never work the same shift, so therefore only one user
will be modifying this file at any time.

My question is, is there any VBA code to determine who
was the last user to modify/save an Excel 2007 file??
Sometimes I would like to see who was the last user to
edit this file.

Thank you!

sorry, meant before save.

--


Gary Keramidas
Excel 2003


"Robert Crandal" wrote in message
...
We work in a networked environment and there are only
2 users who have access to our Excel file. These 2 users
never work the same shift, so therefore only one user
will be modifying this file at any time.

My question is, is there any VBA code to determine who
was the last user to modify/save an Excel 2007 file??
Sometimes I would like to see who was the last user to
edit this file.

Thank you!

robert,
you could create a logfile using notepad application.

See if following is of any help:

'***********Standard MODULE(S)***********
Option Explicit

Public Function LogUserData(LogUser As String)
Dim FilePath As String
Dim FolderName As String
Dim FileName As String

On Error GoTo MakeFolder


AddData:
With ThisWorkbook

FilePath = .Path
FileName = Left(.Name, Len(.Name) - 4)

End With

FolderName = "UserLog"



Open FilePath & "\" & FileName & _
" UserLog.Log" For Append As #1

Print #1, LogUser

Close #1

Exit Function

MakeFolder:
MsgBox (Error(Err))
'folder missing so create it
MkDir ThisWorkbook.Path & "\UserLog"
Resume AddData

End Function

Sub atest()

LogUserData "Tested by " & Application.UserName & _
" " & Format(Now, "dd mmm yyyy hh:mm:ss")
End Sub

Sub OpenNotepad()

Dim FileToOpen As Variant


FileToOpen = Application _
.GetOpenFilename("Text Files (*.log), *.log", Title:="Open
UserLog File")

If FileToOpen < False Then

Shell "notepad.exe " & FileToOpen, vbNormalFocus
End If


End Sub



'*****THISWORKBOOK MODULE****


Private Sub Workbook_Open()
LogUserData "Opened by " & Application.UserName & _
" " & Format(Now, "dd mmm yyyy hh:mm:ss")
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
LogUserData "Saved by " & Application.UserName & _
" " & Format(Now, "dd mmm yyyy hh:mm:ss")
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
LogUserData "Closed by " & Application.UserName & _
" " & Format(Now, "dd mmm yyyy hh:mm:ss")
End Sub
--
jb


"Robert Crandal" wrote:

We work in a networked environment and there are only
2 users who have access to our Excel file. These 2 users
never work the same shift, so therefore only one user
will be modifying this file at any time.

My question is, is there any VBA code to determine who
was the last user to modify/save an Excel 2007 file??
Sometimes I would like to see who was the last user to
edit this file.

Thank you!


.

wb.BuiltinDocumentProperties("Last author")

where wb refers to the workbook

Regards,
Peter T

"Robert Crandal" wrote in message
...
We work in a networked environment and there are only
2 users who have access to our Excel file. These 2 users
never work the same shift, so therefore only one user
will be modifying this file at any time.

My question is, is there any VBA code to determine who
was the last user to modify/save an Excel 2007 file??
Sometimes I would like to see who was the last user to
edit this file.

Thank you!

Thank you very much! It is much appreciated!



"john" wrote in message
...
robert,
you could create a logfile using notepad application.

See if following is of any help:

'***********Standard MODULE(S)***********
Option Explicit

Public Function LogUserData(LogUser As String)
Dim FilePath As String
Dim FolderName As String
Dim FileName As String

On Error GoTo MakeFolder


AddData:
With ThisWorkbook

FilePath = .Path
FileName = Left(.Name, Len(.Name) - 4)

End With

FolderName = "UserLog"



Open FilePath & "\" & FileName & _
" UserLog.Log" For Append As #1

Print #1, LogUser

Close #1

Exit Function

MakeFolder:
MsgBox (Error(Err))
'folder missing so create it
MkDir ThisWorkbook.Path & "\UserLog"
Resume AddData

End Function

Sub atest()

LogUserData "Tested by " & Application.UserName & _
" " & Format(Now, "dd mmm yyyy hh:mm:ss")
End Sub

Sub OpenNotepad()

Dim FileToOpen As Variant


FileToOpen = Application _
.GetOpenFilename("Text Files (*.log), *.log", Title:="Open
UserLog File")

If FileToOpen < False Then

Shell "notepad.exe " & FileToOpen, vbNormalFocus
End If


End Sub



'*****THISWORKBOOK MODULE****


Private Sub Workbook_Open()
LogUserData "Opened by " & Application.UserName & _
" " & Format(Now, "dd mmm yyyy hh:mm:ss")
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
LogUserData "Saved by " & Application.UserName & _
" " & Format(Now, "dd mmm yyyy hh:mm:ss")
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
LogUserData "Closed by " & Application.UserName & _
" " & Format(Now, "dd mmm yyyy hh:mm:ss")
End Sub
--
jb