VBA Code Username/Time/Date/Sheets Stamp Shared Workbook With Macro Buttons

[andhiwilson]

Hi guys,

Hoping forsome guidance on how I can achieve a log of activity to be automatically generatedwithin a shared workbook. Currently I have a functioning code for a single userbut I don’t think it can work for multiple users looking at the workbook at thesame time.

Presently,the below code works as follows:

Workbookopens to specific sheet, the log is stamped with the user name, time and dateof entry, sheet they started on and the fact that this log is for the workbookbeing opened, then the sheet auto saves (initially this was designed to ensurein a shared environment nothing is not over written).

Next whenthe user has finished with the document the log stamps the sheet they were on,time & date, user name and the fact that the workbook is being closed, thenautosaves to ensure the log is saved.

Upon testinghowever if a user enters and leaves the workbook whilst another user has itopen, upon trying to leave the workbook the latter user is faced with aconflict of rows (to accept my changes or others changes) so the concept iscompletely flawed. My code is shown at the bottom of this thread.

My desiredoutcome is for multiple users to be able to go into this workbook at any timefrom a shared location and the following data logged somewhere (does not haveto be within the workbook itself):

User Name
Time and dateon entry
Sheetsvisited (preferably with time spent on each)
Time anddate on exit

Unsure ifthere are some tweaks I can make the code I’m using or whether the there issomething which would work much better / easier from scratch.

All helpappreciated

Andy

DimPreviousValue As String ' For Logging
DimCurrentValue As String ' For Logging
PublicFunction LogChange(Optional Message)
Dim StartTime As Double
Dim TempArray() As Variant
Dim TheRange As Range

Application.ScreenUpdating = False

' How Long Does This Take to Run?
' StartTime = Timer

' Redimension temporary array
ReDim TempArray(0, 5)

' Which row is this going in?
Lastrow =Sheets("Log").UsedRange.Rows.Count + 1

' Set the destination range
FirstCell = "A" & Lastrow
LastCell = "F" & Lastrow

'Store the tracked data in an array
TempArray(0, 0) = FormatDateTime(Now,vbShortDate)
TempArray(0, 1) = FormatDateTime(Now,vbLongTime)
TempArray(0, 2) =Environ$("username")
TempArray(0, 3) =Environ$("computername")
TempArray(0, 4) = ActiveSheet.Name
TempArray(0, 5) = Message

' Transfer temporary array to worksheet
Set TheRange =Sheets("Log").Range(FirstCell, LastCell)
TheRange.Value = TempArray

' Display elapsed time
'MsgBox Format(Timer - StartTime,"00.00") & " seconds"
Application.ScreenUpdating = True
End Function
Private SubWorkbook_Open()
Worksheets("ContentSheet").Activate
Range("D2").Select
LogChange("Opened " & ActiveWorkbook.Name)
ActiveWorkbook.Save
End Sub


Private SubWorkbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.CloseSaveChanges:=True
LogChange("Closed " & ActiveWorkbook.Name)
End Sub