External Data Links Used as Security

[norts55]

If an employee copies a workbook off of the server, can it be unusable because it cannot find its data links? Is there a setting to make this work? Is it possible to use data links from one cell to another to provide some sort of security? - I know a savvy user can find and copy the linked file also but our users are not that savvy.

If needed here is an example...

I have cells in Workbook 1 linked to cells in another workbook called Workbook A.

Wookbook A is stored in a folder on our server so every user who opens Workbook 1 with access to the server is able to get and the see the information linked from Workbook A.

If workbook 1 is copied or moved from our server to an unknown location that does not have access to Wookbook A (on our server), I would like the links to be broken and not visible or usable. - Is this possible?

If there is another way to do this or it is impossible, and it has been discussed in an existing thread, please let me know. Thanks.

 

[6StringJazzer]

I wouldn't try to do this with data links. I would have an Open macro that checks the location of the file being opened, and if it's an unexpected location, hide all the sheets or something. Do you want to inhibit the user from doing anything, or just hide the linked data?

Are you doing this to prevent user error, or to prevent user maliciousness? I know you said that your users are not that savvy, but Excel security features are designed for compliant users. A motivated user can break any security Excel can offer.

 

[norts55]

I am trying to figure out two things...
1. How to stop a current or soon to be ex-employee from stealing our company proprietary spread sheets.
2. If an employee accidentally sends a spread sheet (say thru email) to an unauthorized user, that spread sheet will not open or work.

I like the idea of having Open macro that checks the location of the file being opened, and if it's an unexpected location, hide all the sheets or something. What would be great is instead of just hiding the sheets, it would hide all the sheets then put a password protection on them so they could not be un-hidden. - Is this possible?

 

[6StringJazzer]

Here is the problem with macros. For a macro to run, the user has to allow it. A user can simply prevent macros from running. One option is to have a macro in the file that hides all the sheets upon closing and saving. Another macro will make those sheets visible again on opening, but only if it's opened from the right location. The sheets can be hidden in a state called "Very Hidden." A sheet that is Very Hidden cannot be seen by the user at all; you can only make it visible through the VBA interfaces. The VBA can be password protected so the user cannot get into the code or see the sheets.

Here is a link to a demo file. The VBA password is "test". It will only allow the user to see it in my C drive folder. You can edit the code in ThisWorkbook to make it any folder you want.

File disabled if opened from wrong location.xlsm

Shared with Dropbox

www.dropbox.com

Note that with this method, if the user makes changes, they will be forced to save them on closing the file. If they want to abandon changes, they have to save to a different file name then delete that new file.

But be aware that all kinds of tools and methods exist to override these protections, if someone is sufficiently motivated.

 

[norts55]

I like it but, I have no idea how to write that code.

Maybe this is for a different post but it goes hand in hand with what I am trying to accomplish. Attached is another thought, I just don’t know enough about “groups, user names, permissions, etc.” to know if the end will result in what I am trying to achieve. Please see the attached and let me know if you think the permissions, will give me the result I am looking for. If not, I am very interested in a macro like you showed.

 

[6StringJazzer]

Going back to your original idea about links, the problem is that (to the best of my understanding) Excel saves the latest linked values when saving so that if it reopens and cannot refresh the links, the previous values are still displayed. I don't know of any way to suppress that behavior, although somebody else might know.

Your screenshot isn't high enough resolution for me to read it.

 

[norts55]

Thank you for the response. I am going to leave this one open in the hopes someone might know how to suppress that behavior.

That screen shot is just showing the "Security" Tab of the file Properties. I see there is a "Permissions for SYSTEMS" area within that dialog box. I have never messed with any of that and am curious if that would give me what I am looking for as far as a file not being able to be opened if it left our server.

 

[DROP_DATABASE_MrExel]

Can't U encrypt your document using a VBA code when closing
and decrypt it when opening?
You can set a password to the VBA codes so that nobody gets access to the code unless they put the password.
You could also rebuild the project with another language and not VBA, but that would have a lot of disadvantages.
Another idea would be using a VBA code to acess your company local files, copy a file with a code to execute, and use that code to decrypt the workbook