[paulalawton]

Is there any way I can prevent further copies of a spreadsheet being saved?

I currently share a spreadsheet with 3 other colleagues, and if I am amending it, and they want to use it then this ends with various different copies being saved and not all data being correct.

I want to be able to block anyone accessing the spreadsheet if it is already in use, but as yet have not found a way.

I have tried passwords, but all 3 of us know them so can get around it easily. Any ideas would be appreciated especially as 2 of us are on windows xp professional and one is on vista!!

[StaticFX]

You can, BUT, this will only work if the Tools>Macro>Security is set to low on all the machines. (meaning it allows macro code to run... )

when the workbook is open. Hit ALT+F11
the code window will appear

Double Click "This workbook" in the project window.
A Code window will appear.

Copy/Paste in this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
MsgBox "You cannot choose SaveAs. Only Save is allowed"
Cancel = True
End If
End Sub



then go to the tools menu > Select VBAProject Properties
select the Protection Tab
Enter a Password (that they don't know!) then check the Lock Project for viewing
That will keep them out of the code area

that code blocks the use of SaveAs. Only allows Save

[paulalawton]

Is there any way i can prevent further copies of a spreadsheet being saved??

I currently share a spreadsheet with 3 other colleagues, and if i am amending it, and they want to use it then this ends with various different copies being saved and not all data being correct.

I want to be able to block anyone accessing the spreadsheet if it is already in use, but as yet have not found a way.

I have tried passwords, but all 3 of us know them so can get around it easily. Any ideas would be appreciated especially as 2 of us are on windows xp proffesional and one is on vista!!!

Thanks for your reply, unfortunately it did not work... does anyone else have any further ideas??


Sorry - it did work - I was just not looking correctly - duh!

[StaticFX]

It will work, but like I said. The other users have to have low security on the macros. If they change it back to high or medium, they can block the code which will then allow them to save as..

[JBeaucaire]

I would recommend you rethink this whole setup. Having multiple people access the same document and not TRAINING THEM to leave it alone when someone else is using it is just nuts.

Best would be for each of them to have documents of their own they could open at anytime and put in the data they need to update, and then you have a separate document that gathers the info from each of their spreadsheets as it opens to bring it all into one place for you to view/print/report on the results. This is way safer and not hard at all to setup.