Saving the file automatically with the last person to close or view the file

[bearcub]

Is it possible to create a macro that will display in a cell the last person to view that file and to save it? Sometimes someone might open a file but doesn't make any changes. I was planning on turning on the track changes but I'm not sure if some of Excel's functionality is lost by doing this.

Plus, I would like to use a cell on the worksheet to notify the next user who opens the file to know who just worked on it (and perhaps the day and time?

Can VBA do this?

Michael

 

[jorismoerings]

Hi,

try something like this.
It will check if a sheet exist or else creates one, and insert username, filesize and a date/time stamp of the save action in a table.

Copy the code onto the "this Workbook" section of the workbook.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   
    Dim i As Integer, blnFound As Boolean
    blnFound = False
    
    Application.ScreenUpdating = False
    
    With ThisWorkbook
        For i = 1 To .Sheets.Count
            If .Sheets(i).Name = "Log-Info" Then
                blnFound = True
                Exit For
            End If
        Next i
        
        If blnFound = False Then
            .Sheets.Add
            With ActiveSheet
                .Name = "Log-Info"
                Cells(1, 1).Value = "Date & Time Stamp"
                Cells(1, 2).Value = "Saved By User"
                Cells(1, 3).Value = "File Size"
            End With
        End If
    End With
       
    Sheets("Log-Info").Select
    Range("A2:c2").Insert Shift:=xlDown

    Cells(2, 1).Value = Date + Time
    Cells(2, 1).NumberFormat = "dd/mm/yy hh:mm"
    Cells(2, 2).Value = Application.UserName
    Cells(2, 3).Value = (FileLen((ThisWorkbook.Path) & "\" & (ThisWorkbook.Name)) / 1024)
    ThisWorkbook.Worksheets("Log-Info").Cells.EntireColumn.AutoFit

    Application.ScreenUpdating = True

End Sub

Hope this helps.