Workbook Protection, delete all sheets if file name doesn’t match given name.

Hi all,

I have a workbook which I’m happy with the levels of protection.
For starters I have a ‘splash sheet’ which is the only sheet shown when opening the file.
A box then pops up asking for a password (I’ve called it ‘Licence Key’). This password is located on a very hidden sheet that never pops up. If the password is correct they then get a message telling them how long the have (days) until it run out.
The splash sheet then goes to very hidden and the other tabs appear.

This all works great. However, I would like one more level of protection. I would like all the sheets to be deleted and only a splash sheet saying something like “This is a copied file” to remain. I will add the file name to the same sheet as the password (say “Sheet 1, Cell D5”

Obviously this will mean that they can’t copy the file and paste in the same folder although they could copy it and paste it in another. This is something I haven’t figured out yet.

Any advise? Macros or tips to help me with this would be great.

Thanks

The mechanism could look like this:
1. When you run the file for the first time, in a hidden sheet (or even in the system registry) save the path where the file is located (Thisworkbook.Path)
2. During the next startups, check if the file location is the same as the saved one. If not, take drastic action.

Artik

Originally Posted by Artik

The mechanism could look like this:
1. When you run the file for the first time, in a hidden sheet (or even in the system registry) save the path where the file is located (Thisworkbook.Path)
2. During the next startups, check if the file location is the same as the saved one. If not, take drastic action.

Artik

That makes sense. So they can only save it once in one place. That’s a really good option. Obviously if they disable macros then this will just show the splash sheet but they could still get in my remove the project protection (if they could figure it out) but I plan on countering that by making some of the formulas incorrect on start up and having a marco change them. So they’ll need macros.

Any clue on a macro for this protection?

Thanks for the response and idea. It’s great.

Originally Posted by TMS

Care to share your project, suitably desensitized? I'm interested to see how easy it is to break into ... so no passwords needed.

Hey TMS, yes I’d love you. Give me 5 mins and I’ll post it. Appreciate the help on this and any learning is good for me.

@TMS, attached. I have removed all formulas. Please have a go and give me any feedback you have.

File keeps failing to upload...... any ideas as to why?

I've had to delete a lot of sheets as the files was huge.

Thanks for the feedback..

Is your file larger than 1000 KB? Because this is the limitation of the forum. Are you following the yellow banner at the top of the page?

..::Edit
The questions are no longer valid ::..

Artik

Originally Posted by Artik

Is your file larger than 1000 KB? Because this is the limitation of the forum. Are you following the yellow banner at the top of the page?

Artik

Yes it was 6.2MB, removed some tabs now and it’s on there.

Thanks

Is the published project to be secured or should we break in?

Artik

Originally Posted by Artik

Is the published project to be secured or should we break in?

Artik

You can break it.

I've already done that.

Artik

Originally Posted by Artik

I've already done that.

Artik

Okay, I thought you would. I know nothing is 100% secure but what else can I do to protect it? You can’t see it because I’ve removed all the sheets and formulas but I’ve put a lot of effort in and I want to make it so people can only get a copy from me for a set time frame.

Ideally this would happen

- they get a copy of the workbook that can’t be copied
- they have a set time frame to complete it
- after that time frame all the formulas are removed
- all sheets are then Unlocked
- the parameters tab and splash sheet are deleted
- the macros are removed (not that important)
- the file then save as an excel workbook (not macro enable)

Then they are left with the result of their work but can’t use it again.

So not to hard then?

If you cancel the password box only the slash sheet should be visible…??

In fact, when you cancel the Password box it should close the file?

When I cancel the password box it say “this key has not been issued” and makes you close the file, which a save option.

Okay, let me know when you have change to explain. Thanks again.

Firstly, thank you for your time and explanation, it's given me a level of confidence in my protection. I actually agree with the fact that locking them out past the date is probably extreme. That's why I've been thinking of just removing the formulas and deleting the parameters sheet. Problem with that is that is where the passwords are so i would also have to removed that macro. So essentially, once past the date it just turns into a workbook with data. If thats possible.

Thanks again, if i remember correctly this isn't the 1st time you've helped me.

For several years, by default, files downloaded from the Internet (www or e-mail) receive the MOTW (Mark of the Web) attribute. After opening the file in Excel, a message asking for permission to open the file appears in the yellow bar. Expressing consent removes the MOTW attribute. The file initially opened in Protected View is reopened in "Normal Mode". At this point, when the Workbook_Open event is programmed, an error may occur because the engine does not "see" the properties and methods of the application yet. This is an issue that has been confirmed by MS. Therefore, it is recommended to use a structure similar to the following. Suppose we originally had a procedure:

Please Login or Register  to view this content.

After the change, it should look like this:

Please Login or Register  to view this content.

In my opinion, the order of calling procedures in Workbook_Open should be different. First OpenBook, then ShowAllSheets. It will probably require some other minor changes. I can't understand why if you enter the wrong password (or press Cancel) you are asking the user to save the changes. A bad password is to close the file without saving your changes (and without asking). In addition, I believe that the data in the PARAMETERS!I2:L5 range should be encrypted (not only columns J and L, but also I and K) in order not to give the burglar everything on the tray.

Originally Posted by Kevin Rush

That's why I've been thinking of just removing the formulas and deleting the parameters sheet. Problem with that is that is where the passwords are so i would also have to removed that macro.

You can replace formulas with values by looping through all sheets and by looping through all cells containing formulas. Or, if the formulas are in continuous ranges,

Please Login or Register  to view this content.

After all the destructive activity, save the file as XLSX. And an XLSM file should commit suicide.

Please Login or Register  to view this content.

Artik

Originally Posted by Artik

For several years, by default, files downloaded from the Internet (www or e-mail) receive the MOTW (Mark of the Web) attribute. After opening the file in Excel, a message asking for permission to open the file appears in the yellow bar. Expressing consent removes the MOTW attribute. The file initially opened in Protected View is reopened in "Normal Mode". At this point, when the Workbook_Open event is programmed, an error may occur because the engine does not "see" the properties and methods of the application yet. This is an issue that has been confirmed by MS. Therefore, it is recommended to use a structure similar to the following. Suppose we originally had a procedure:

Please Login or Register  to view this content.

After the change, it should look like this:

Please Login or Register  to view this content.

In my opinion, the order of calling procedures in Workbook_Open should be different. First OpenBook, then ShowAllSheets. It will probably require some other minor changes. I can't understand why if you enter the wrong password (or press Cancel) you are asking the user to save the changes. A bad password is to close the file without saving your changes (and without asking). In addition, I believe that the data in the PARAMETERS!I2:L5 range should be encrypted (not only columns J and L, but also I and K) in order not to give the burglar everything on the tray.
You can replace formulas with values by looping through all sheets and by looping through all cells containing formulas. Or, if the formulas are in continuous ranges,

Please Login or Register  to view this content.

After all the destructive activity, save the file as XLSX. And an XLSM file should commit suicide.

Please Login or Register  to view this content.

Artik

Thanks,

I have a macro that removes all the formulas, so I can just call that macro.
The. I would then need the file to save as XLSX. Following that I need the ‘suicide’.

I will try this now.

Originally Posted by TMS

Never been one for using macros to delete macros.

Deleting code with a macro is riddled with great reefs. Programmatic access to the project must be enabled, which is disabled by default. You cannot remove code from a module where the macro is currently executing. And if that wasn't enough, there are big problems with programmatic unlocking of project. Therefore, it is much easier to save the file as XLSX.

Artik