Log when workbook opened, even if read only

[Gabbs]

Hello all,

I have code in a workbook that automatically creates a log of who has opened the workbook and the date on a designated worksheet, code below. Is there a way I can modify this so that even if the workbook is opened as read only, the log will still be created? I want to lock modifications with a password in the tools>general options field of the save process but still want the log to generate as different users open the workbook, whether as read only or with the password to allow modifications.

Thank you in advance!
George

Private Sub Workbook_Open()


Dim Lrow As Single
 
Lrow = Worksheets("Gradebook Log").Range("A" & Rows.Count).End(xlUp).Row + 1
 
Worksheets("Gradebook Log").Range("A" & Lrow).Value = Now

Worksheets("Gradebook Log").Range("B" & Lrow).Value = Application.UserName
 
ThisWorkbook.Save

End Sub

 

[mole999]

even if you set the sheet as xlveryhidden, if the user starts with macros disabled you won't get an entry. You need to close the book down to a single page which says the project needs to have macros enabled, only when the macros are enabled then the first check is to hide that sheet and present the book as normal

 

[steve the fish]

You cant save a read only workbook. Its read only.

 

[Gabbs]

Thanks all for the feedback, I made some modifications and this is where I am at now. Obviously, I changed the code to run on close rather than open which I am more than ok with and also included some code to change to readwrite, make the log entry, and then back to read only. Two issues with how this is running now.

Firstly, the saved file is automatically being moved from a network drive (where it currently lives) to My documents. Is there a way to direct VBA SaveAs to save in the same location as the current workbook, even if that won't always be the same location from day to day? Second, it prompts that a file already exists in this location and asks if you want to save over. I obviously do want to save over, is there a way to suppress this prompt and to automatically save over the existing file?

Again, thanks so much!
George

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Lrow As Single

If ActiveWorkbook.ReadOnly Then
ActiveWorkbook.ChangeFileAccess Mode:=xlReadWrite
Lrow = Worksheets("Gradebook Log").Range("A" & Rows.Count).End(xlUp).Row + 1
Worksheets("Gradebook Log").Range("A" & Lrow).Value = Now
Worksheets("Gradebook Log").Range("B" & Lrow).Value = Application.UserName
ThisWorkbook.SaveAs WriteResPassword:="password"


Else

Lrow = Worksheets("Gradebook Log").Range("A" & Rows.Count).End(xlUp).Row + 1
Worksheets("Gradebook Log").Range("A" & Lrow).Value = Now
Worksheets("Gradebook Log").Range("B" & Lrow).Value = Application.UserName
ThisWorkbook.SaveAs WriteResPassword:="password"

End If

End Sub

 

[Jaafar Tribak]

How about creating the log as a text file loacted in a shared location

As for suppressing the save propmpt you could precced the saving with turning off DisplayAlerts like this :

Application.DisplayAlerts = False
    ThisWorkbook.SaveAs WriteResPassword:="password"
Application.DisplayAlerts = True

 

[Gabbs]

That worked really well for addressing the prompt/save over, Jaafar! Thank you! I have thought about the separate text file for a log but having everything in one document would make things much more streamlined for an eventual save as pdf which is required. Any ideas for directing VBA to save in the current filepath?