Looking for a way to log (on a sheet within a workbook) all users that
access the spreadsheet and when.

Is this possible using wither VBA or other method?

Many thanks.

DT

Do the users have access to a share that they can write to?

If yes, you could have a macro that adds a line to a tab delimited text file
when they open the file (macros must be enabled, though).

Rightclick on the excel icon to the left of the File option (on the worksheet
menubar). Select view code and paste this into the code window:

Option Explicit
Private Sub Workbook_Open()

Dim LogDir As String
Dim LogFile As String
Dim myFileNum As Long
Dim testDir As String

LogDir = "\\servername\sharename\foldername"
LogDir = "C:\my documents\excel"
LogFile = LogDir & "\log.txt"

testDir = ""
On Error Resume Next
testDir = Dir(LogDir, vbDirectory)
On Error GoTo 0

If testDir = "" Then
'not connected or spelling error!
Exit Sub
End If

myFileNum = FreeFile()
Open LogFile For Append As #myFileNum
Print #myFileNum, ThisWorkbook.FullName & vbTab _
& Application.UserName & vbTab & fOSUserName & Now
Close #myFileNum

End Sub

Then right click on the project (hit ctrl-r to see it if its not shown). Select
Insert, then Module.

Paste this in:

Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX < 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
End Function

=====
When you want to view the log, just open excel and do File|open--choose
delimited by tab when you see the import wizard.

DT wrote:

Looking for a way to log (on a sheet within a workbook) all users that
access the spreadsheet and when.

Is this possible using wither VBA or other method?

Many thanks.

DT

--

Dave Peterson