Preventing people from opening developer mode, cut and paste or even deleting workbook

[nagaita]

Hi,

My excel workbook contains info that I want to let selected people see and it's stored in the office servers. Basically, no one should be able to print, save or delete the whole workbook and any of its content. But, others (who have the password to open the workbook) have been copying and pasting the workbook onto other drives/their own PCs and some of them have even deleted the file.

I have inserted a code in the VBA mode that prevents "save" or "save as" via the design mode and protected all the VBA projects with a password. The sheets are also protected from copy and paste of the cells. However, it seems that anyone who knows how to open the design mode will be able to "save as/save" making my VBA code useless.

By opening in design mode, they can choose "save as" and still save the workbook onto their computers

I was wondering if you guys know way that
1. Prevents deletion of my entire workbook
2. Prevents cut/copy and paste of the workbook
3. Runs a code, preventing design mode from being accessed in others' computers.
4. Makes sure that where the location that they open the workbook from is not changed (i.e. not copied location)

Thank you for taking the time to look through this long message and I would really be grateful for any help at all!!

[TMS]

Sadly, your VBA Code IS useless. All they have to do is open the workbook without enabling macros and there is no protection at all.

Excel is not a secure environment. If your data is sensitive or the workbook contains your Intellectual Property, you cannot rely on Excel security or VBA to protect it.


Regards, TMS

[nagaita]

Managed to find a code that forces them to open macros in order to view other (hidden) sheets. I've tried this and it prevents printing, copying and pasting a cell.

Please Login or Register  to view this content.

But when i tried to add in a "no save" function, it gives a compile error (Ambiguous name detected: Workbook_Open)

:/
This was what I added to the end of the above code to disable the saving function:

Please Login or Register  to view this content.

Funny thing is that when I paste on a new workbook, it works!

[mgs73]

You can't really stop someone who knows what they are doing from doing these things, but you can make it inconvenient.

1. Prevents deletion of my entire workbook

This can't be controlled by Excel or VBA - you'd have to talk to your network administrator about access rights.

2. Prevents cut/copy and paste of the workbook

This can be done - code below:

Put this into ThisWorkbook

Please Login or Register  to view this content.

and this into a standard module

Please Login or Register  to view this content.

3. Runs a code, preventing design mode from being accessed in others' computers.

Don't think this is possible.

4. Makes sure that where the location that they open the workbook from is not changed (i.e. not copied location)

You can add something like this to Workbook_Open (the reason you are getting the Ambiguous name detected error is your code has two Workbook_Open sections - you can only have one.)

Please Login or Register  to view this content.

In addition to doing this, you should hide all sheets (except one that says you have to enable macros) on close, so that when opened, if macros are not enabled, nothing in the spreadseet is visible, except the message that you need to enable macros. If macros are enable, unhide all sheets and hide the one that says macros have to be disabled.

[TMS]

You already have a workbook open event in the code you found to "force users to enable macros". You need to combine the two lines of code in the second version with the first.

I don't want to rain on your parade, but it won't stop anyone with a little bit of knowledge. If you can find code to hide sheets,they can find code to unhide them.

When you have a prototype, before it contains any sensitive data, maybe you'd like to post a copy to see what we can do with it?

All that said, it is a useful routine and it will stop a lot of people, but not all. And some will see it as a challenge.

Regards, TMS

[Kyle123]

How you going to stop print screen?