Ensure certain file is used?

[TheWennerWoman]

Hello, this is a corporate environment and we have a number of macro-driven files on a Sharepoint site.

I need to ensure that the end-user is downloading one of these files each and every time they want to use it (a health claim form, for example)......what I don't want them doing is saving down a file on their desktop and re-using it.

Is there a way of getting Excel to detect if this has happened?

I thought something like having the original file with a flag somewhere of 0; when the user opens it, the flag changes to 1. If they then attempt to subsequently reuse the file, I can have some code detect the 1 and say "can't use this file, download a fresh copy from Sharepoint". Except these forms need to be approved by line managers so if the line manager opens the form, the flag will be 1 and the error message triggered (which isn't what I want in those cases).

Any thoughts appreciated.

Thanks for reading.

 

[Micron]

These "files" are workbooks? Then I would try examining the workbook path to ensure it begins with your SP server path. But where are managers opening them from? If not from SP then your code would need to include a test for mgr name. However, if you hard code those names, that would require re-design every time a name changes. If you hide a sheet of mgr names and use that instead, you'd need to protect that from viewing/editing as well. Also, if you can code any solution anyone can edit code unless you password protect the vba project. In that case, you better not forget the password.

In the end, if I was one of these users who are too lazy to do the right thing, the protection you install may not need to be bullet proof, but just enough to make it not worth my while.

 

[TheWennerWoman]

These "files" are workbooks? Then I would try examining the workbook path to ensure it begins with your SP server path. But where are managers opening them from? If not from SP then your code would need to include a test for mgr name. However, if you hard code those names, that would require re-design every time a name changes. If you hide a sheet of mgr names and use that instead, you'd need to protect that from viewing/editing as well. Also, if you can code any solution anyone can edit code unless you password protect the vba project. In that case, you better not forget the password.

In the end, if I was one of these users who are too lazy to do the right thing, the protection you install may not need to be bullet proof, but just enough to make it not worth my while.

Yes they are workbooks.

How it *should* work is the user downloads a fresh copy from Sharepoint, completes it, forwards it to his/her line manager for authorisation who then attaches it to an email with a yes/no response.

So it's messy.