Disabling Save/Save As for Excel 2010 Workbook

[riffraf]

I'm working with a client who has had version-control issues with their financial models. I created a button so that the workbook saves with a version number and date into a specified folder. I'd like for this to be the only way to save the workbook. I used the code in the ThisWorkbook section of the editor:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
End Sub

This does the job when the model is the sole workbook open.

However, say I have another workbook open (as is typically the case) and then decide to open a version of the model I've saved in the past using my button. Now I can save/save as without using the button I created. This is a problem because someone could update things in an old model without creating a new version number and save over the old version or could save it to a location outside of where it should be saved.

Has anyone run into this before? How can I disable the save/ save as features even if my client's model is not the only workbook open?

Thanks very much.

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I don't think you will be able to do what you want with Excel macros. You really need to look at files permissions. Even with macros file deletion and renaming will still be possible. As will overwriting one file with another.

A product like Microsoft Sharepoint will keep versions for you but if you don't already use it it will be overkill (and expensive!). Otherwise you need to look at making things read-only and looking at this type of thing:

Prevent File Deletion and File Renaming in Windows

Controlling workbook access in Excel with Information Rights Management - Excel

 

[riffraf]

Thanks for the reply. The client has Sharepoint, so I'll try to incorporate that.