Log of each person that saved a file

Q

QuietMan

Is there a way that I can keep a log of each person that saved a file and the
date and time

I use this code below but the area that the information is stored could be
changed if discovered...any help would be appreciated

Private Sub Workbook_BeforeSave(ByVal SaveAsUI _
As Boolean, Cancel As Boolean)
Sheets("Control").Range("V19").End(xlDown).Select
a_row = ActiveCell.Row
Sheets("Control").Range("V" & a_row + 1).Value = "Last Saved By " &
Environ("UserName")
Sheets("Control").Range("W" & a_row + 1).Value = Date
Sheets("Control").Range("X" & a_row + 1).Value = Time
End Sub
 
J

Jacob Skaria

You dont need to select the sheet or cell; instead try the below code..You
can have a separate sheet for logs which can be hidden...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI _
As Boolean, Cancel As Boolean)
Dim a_row As Long
a_row = Sheets("Control").Cells(Rows.Count, "V").End(xlUp).Row + 1
Sheets("Control").Range("V" & a_row) = "Last Saved By " & Environ("UserName")
Sheets("Control").Range("W" & a_row) = Now
End Sub
 
Q

QuietMan

Thanks,

Is there a way I can hide the sheet so it's not vissable to the users as the
reason I'm doing this is because there arguments about who saved the file last
 
R

Ryan H

Sheets("Sheet1").visible =xlVeryHidden

or just Right Click the the sheet tab and select hide.

Note: If you dont want the user to see the worksheet then I would suggest
protecting the workbook with a passord so the user can not unhide your sheets.

Hope this helps! If so, let me know, click "YES" below.
 
P

Patrick Molloy

each sheet has a Visible property. you can set this to xlSheetHidden or
xlSheetVeryHidden as well as xlSheetVisible
the very hidden method means the user would have to go into the IDE and
examine the sheet properties, or write a line of code to make the sheet
visible. A hidden ( as opposed to Very Hidden) can be unhidden via the
Format/Sheet Unhide menu
 
R

Ryan H

If you want you could use this and it would ensure the sheet is hidden each
time the workbook is saved.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim a_row As Long

With Sheets("Control")
a_row = .Cells(Rows.Count, "V").End(xlUp).Row + 1
.Range("V" & a_row) = "Last Saved By " & Environ("UserName")
.Range("W" & a_row) = Now
.Visible = xlVeryHidden
End With

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top