Stopping workbook being copied to desktop

[Swifty87]

Hey All,

I am wondering if someone can help. Is there a way that if a file is copied or moved to a location other than the one i set in-bedded into the VBA it will pop up a message saying "Workbook should not be moved or copied from shared location". And then close?

Reason is i have created a file that tells people what coating to use on certain machined parts, there are multiple procedures referenced and these are revised regularly, what i don't want to happen is that someone saves the file to their desktop from the shared folder revisions change and the wrong coating is applied costing many ££.

Any help would be greatly recieved

 

[Joe4]

Welcome to the Board!

I found a similar question, but it looks like there are "loopholes" and ways to get around it: https://stackoverflow.com/questions...g-a-file-to-any-directory-except-one-specific

If there are values that are revised regularly, maybe you should create a "master" Excel file that holds all the values, and all the other workbook reference/get their values from that one. So the values on the individual workbooks aren't hard-coded, but rather linking formulas to the "master" workbook. Then you never have to worry about a copy having an our-dated value.

 

[Swifty87]

Welcome to the Board!

I found a similar question, but it looks like there are "loopholes" and ways to get around it: https://stackoverflow.com/questions...g-a-file-to-any-directory-except-one-specific

If there are values that are revised regularly, maybe you should create a "master" Excel file that holds all the values, and all the other workbook reference/get their values from that one. So the values on the individual workbooks aren't hard-coded, but rather linking formulas to the "master" workbook. Then you never have to worry about a copy having an our-dated value.

Hey Joe,
Thank you for the welcome and the link just had a read. Regarding the Master file i had never thought of that!! i will set that up and try using that.

For my benefit and curiosity, after reading the save as thread, rather than preventing the user to save as can we add in logic that the file will open check where it is stored and if it is not the set location eg "N:\plant_0166\Vam Coating Wizard" (shared drive). it will close down automatically.

Once again thanks for the reply

 

[Joe4]

For my benefit and curiosity, after reading the save as thread, rather than preventing the user to save as can we add in logic that the file will open check where it is stored and if it is not the set location eg "N:\plant_0166\Vam Coating Wizard" (shared drive). it will close down automatically.

You can use a "Workbook_Open" event procedure (which is VBA code that runs autoamtically when the file is opened) to do something like that. However, that is dependent on them enabling VBA. If VBA is disabled, it will open without the VBA code running. One way I have seen people try to address that is have VBA automatically hide all the sheets when closing, and password protect it. Then have the VBA code in the Workbook_Open unprotect and unhide the data. So if they don't enable VBA, they won't be able to see anything.

Note that in order for that to work, you need to make sure that the initial file you create has all the needed VBA code, and the data is hidden from the get-go.

 

[Swifty87]

You can use a "Workbook_Open" event procedure (which is VBA code that runs autoamtically when the file is opened) to do something like that. However, that is dependent on them enabling VBA. If VBA is disabled, it will open without the VBA code running. One way I have seen people try to address that is have VBA automatically hide all the sheets when closing, and password protect it. Then have the VBA code in the Workbook_Open unprotect and unhide the data. So if they don't enable VBA, they won't be able to see anything.

Note that in order for that to work, you need to make sure that the initial file you create has all the needed VBA code, and the data is hidden from the get-go.

Perfect thanks Joe, i will go with the master file, but will have a play around with this for my interest. My file runs depended on VBA macros so without enabling them it is a useless sheet anyway.