How to Stop People Overwriting Excel Spreadsheets

[Caleeco]

Hello,

Question is in bold below!

I've recently been pulled into a new team at work. There is a spreadsheet we all use and populate/update with various bits of data many times a day.

One of my biggest frustrations (already!) is people overwriting changes I make to the spreadsheet because they mistakenly work on it in [Read Only] mode. They realise this once it comes time to save... but instead of making their changes to the live version, they just SaveAs over it (potentially removing other peoples changes).

Is there a way to log if someone does a SaveAS for a Read Only spreadsheet and overwrites changes to the "Live" Version?

Any help would be appreciated.

Many Thanks
Caleeco

 

[Steve_R]

Hazy meory but I used to run a full audit of who used my sheets and when years ago. I did not use it to trap the guilty but told people I was logging details in a way they could not uncover. Just the warning was enough. I never needed to refer to the audit trail.

Something like this in the Before Save event, perhaps?

UNTESTED

Sub test()


NewRow = Worksheets("Sheet1").Range("A1").Cells(Columns.Count).End(xlUp).Offset(1).Row

Sheets("Sheet1").Range("A" & NewRow) = Now()

' you may need to try a couple of methods to grab user name
Sheets("Sheet1").Range("B" & NewRow) = Application.UserName
'or
Sheets("Sheet1").Range("C" & NewRow) = Environ$("UserName")


End Sub

At the time, I did not use either of the above but grabbed details from the LAN environment using code from one of the MVPs that gbabbed both name and LogonID but I can't recall whose site it was.
Code on the following site looks familiam but I no longer work from a LAN so cannot test that for you.
LAN User ID's accessing Excel Spreadsheet - VBA Visual Basic for Applications (Microsoft) - Tek-Tips

Set your sheet Very Hidden and lock the code so nobody knows you are doing it.

 

[Caleeco]

Hi,

Thanks for the sample code. I am looking to setup a logging system. I'll probably expand on yours to monitor cell changes (Need to do some testing of it, make sure the workbook doesn't run slowly).

I guess if I come to realise my changes have been overwritten, I can narrow down the potential offenders.

What would be better, would be the following pseudo-code

Event: SaveAS command
If Workbook is "Read Only" 
     Log username, time and filename
Endif

However, I don't know if an event based macro will even run on spreadsheet that's opened in Read Only mode

Need to do some more reasearch...

 

[Steve_R]

My problem was a little different to yours. My problem was failure to close workbooks. I ran mine off workbook open and workbook close events, matching the ID of whoever closed it to their last open record.
Despite no more problems I eventually decided to autoclose after 10 minutes activity. However, I did maintain the usage tracking sheet, including columns that tracked other things.

Something like the following in workbook open event may detect if the WB is opened in Read only mode

Untested pseudo

Warnings off
WBReadOnly = False
On error goto WBSaveError
<code>WB.Save

WBExit:
Warnings on
Exit Sub
</code>
WBSaveError:
Select case err
case {relevant number}

WBReadOnly = True
<code>case else
{whatever}
End Select
goto WBExit

</code>

 

[Caleeco]

^^ im not intelligent enough to decipher your pseudo code

I was playing around with this... Doesnt seem to recognise the file is opened in read-only for some reason

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim NewRow As Long
Dim Filename As String




NewRow = Worksheets("SaveLog").Range("A1").Cells(Columns.Count).End(xlUp).Offset(1).Row
Filename = ThisWorkbook.FullName
    If GetAttr(Filename) And vbReadOnly Then
        'MsgBox "File is Read-only"
        Sheets("SaveLog").Range("A" & NewRow) = Now()
        Sheets("SaveLog").Range("B" & NewRow) = Application.UserName
        Sheets("SaveLog").Range("C" & NewRow) = Environ$("UserName")
    Else
        MsgBox "File is not read-only"
    End If


End Sub

 

[Steve_R]

As I am working from home, I am hamstrung to test it. I cannot test for opened 'read only' lock by other user.
What I had in mind above was was
- Public Boolean - WBReadOnly
- When the WB is first opened test save (i.e. before a user does anything)
- If an error exsists for attempting to save a WB when opened in Read only mode is should be trappable
- Presuming there is no error in a workbook opened in normal mode, code flows straight to exit
- Turning warnings off and on will prevent the user seeing any message when the WB opens read only.
- Later, when the user attempts to save and the workbook is WBReadOnly, trap using the save event, force save only (use WBname Environ.username and date/time as basis for filename); tell user what you did.

Problem - I'm not certain whether attempting to save a read only WB results in a a trappable error. You would need to test I am happy to wirte the code if you wish but will be unable to test it

*******************************************************************************************************************************

I was unaware of vbReadOnly attribute which seems a better way to solve the problem if it works.

Sub Test()

Filename = ThisWorkbook.FullName
    Debug.Print "Attrib = "; GetAttr(Filename)
    Debug.Print "VbReadOnly = "; vbReadOnly
    Debug.Print Environ("UserName") 'PC logon name 
End Sub

My result from a non-read only file
Attrib = 32
VbReadOnly = 1

In VBA FALSE is zero (0). TRUE can be any non-zero value (including negative)

Therefore in the code above my test evaluated.
TRUE and TRUE = TRUE

In other words VbReadOnly returned TRUE despite it being a workbook opened in normal mode.

I suggest you save the test I posted above in a test workbook. Get another user to open the file so it is read only when you open. Run the code and look at the results.
NOTE when I tested it in an unsaved WB, Attrib caused an error. Bearing in mind the list of attributes on the list below does not contain a zero and only zero is FALSE, I don't understand how GetAttr(Filename) is essential to the test you need (but did find similar code on the web that also tested GettAttr(Filename)).

List of file attributes
https://msdn.microsoft.com/en-us/library/microsoft.visualbasic.filesystem.fileattr(v=vs.110).aspx

 

[Steve_R]

I thought of the switch to open a workbook read only

For me, the shortcut is - "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" /r "c:\temp\temp.xlsm"
Where temp.xlsm is saved to c:\temp
I am using Office 2010 23 bit version (but system if 64 bit)

The following works exactly as expected when I did the pseudocode (variablesare different)

Public RO As Boolean ' put this at the top of a standard module, not the workbook module
Private Sub Workbook_Open()

Application.DisplayAlerts = False
 RO = False

On Error GoTo Workbook_Open_Error
ActiveWorkbook.Save

Workbook_Open_Exit:

Application.DisplayAlerts = True
MsgBox "RO = " & RO
Exit Sub

Workbook_Open_Error:
Select Case Err

    Case 1004
        MsgBox "error trying to save"
        RO = True
    Case Else

End Select
GoTo Workbook_Open_Exit
End Sub

RO is returning blank when Msgbox RO is tested from a public variable. Hazy memory is you need to declare it in a standard module, not the workbook module. Tested that way, it seems to hold its value.

Code posted previous still returns 32 and 1 in the WB I tested the above code in when opened with the read only switch.
In other words, VbReadOnly will not do what you want.