Stopping workbook being copied to desktop

Swifty87

New Member
Joined
Oct 23, 2019
Messages
10
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,429
Office Version
365
Platform
Windows
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/52121695/how-to-prevent-the-user-from-saving-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

New Member
Joined
Oct 23, 2019
Messages
10
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/52121695/how-to-prevent-the-user-from-saving-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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,429
Office Version
365
Platform
Windows
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

New Member
Joined
Oct 23, 2019
Messages
10
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.
 

Forum statistics

Threads
1,081,678
Messages
5,360,462
Members
400,586
Latest member
Minty

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top