Worksheet Access Log

[narrowgate88]

I want to create a log of everyone who opens a particular workbook. I'm using Excel 2003. I found this macro, and created a worksheet called "Log", but I've opened the workbook several times and nothing appears on the Log sheet. What am I missing?

Private Sub Workbook_Open()
Worksheets("Log").Range("A65536").End(xlUp).Offset(1, 0).Value _
    = Format(Now(), "mm-dd-yy HH MM AMPM")
Worksheets("Log").Range("B65536").End(xlUp).Offset(1, 0).Value _
    = Environ("UserName")
End Sub

[hutch@edge.net]

I tested it in a workbook and it worked fine. Be sure you have th code in the "This Workbook" module, as in the attached test.

[narrowgate88]

I didn't have it in "This Workbook" so now it works. Now, because I've added this, it now asks the user if they want to save changes. Can I get it to bypass this if the only change that was made was updating the log?

[hutch@edge.net]

You'll probably need a real guru to answer that one. I'm just a newbie.

It seems to me you could add code at the bottom of the workbook open macro that would automatically save the changes after the log file was writtent to, so it wouldn't ask if there were no other changes made. That would cause a prompt to show asking where to save the workbook, however, if the workbook doesn't exist on their computer.

I tried adding "SaveChanges = True" to the end of the code. That didn't do it.

[hutch@edge.net]

Try this:

Private Sub Workbook_Open()
Dim sPath As String, sFname As String
Worksheets("Log").Range("A65536").End(xlUp).Offset(1, 0).Value _
    = Format(Now(), "mm-dd-yy HH MM AMPM")
Worksheets("Log").Range("B65536").End(xlUp).Offset(1, 0).Value _
    = Environ("UserName")
Application.DisplayAlerts = False
    sFname = ActiveWorkbook.Name
    sPath = ActiveWorkbook.Path
    ActiveWorkbook.SaveAs (sPath & "\" & sFname), _
            FileFormat:=xlNormal
Application.DisplayAlerts = True
End Sub

[narrowgate88]

Yes, that worked! Not bad for a newbie. Thanks so much.