Disable copy of worksheets and renaming of certain sheets

[Lazarus Rises]

Hi guys,

A- Is there a way to disable the copy of any worksheet in a workbook? As in via the right click Move or Copy option. I am OK with moving just not copying it.

B - Is there a way to disable to move and copy function for a select group of sheets (4 sheets in total)?

C- Also is there a way to disable the renaming of selected worksheets (4 Sheets in total)?

Thank you.

[hrlngrv]

You can use workbook protection to protect the structure of a workbook. See the MSFT docs for details.

[Lazarus Rises]

Yes, I am aware, but I want to be able to rename or move some sheets.

[Lazarus Rises]

OK,
so I seem to have found a way to protect the sheet name on selected sheets using the code below.

Just paste it into the relevant sheet and change "Master" to suit your needs.

It will allow you to type in a new name on the sheet, but as soon as you click on the sheet it reverts back to the desired name.

If you can think of a better way, then by all means please share.

PS: Still looking to disable the "copy sheet"?

Please Login or Register  to view this content.

[hrlngrv]

Depends on the cleverness of one's users. Open a different workbook and run a macro including the statement

Please Login or Register  to view this content.

and your event handlers won't do anything.

If you mean prohibit copying worksheets into other workbooks, you may need to look into DRM.

[Lazarus Rises]

I added the following to the workbook, but didn't work.

Please Login or Register  to view this content.

I just don't want to copy any sheets within the same workbook.

Thanks.

[Lazarus Rises]

Just bumping this up.

Hoping someone has a solution to disable the copy of sheets.

[hrlngrv]

The only relatively robust way to prevent copying worksheets within the same workbook is to protect the workbook's structure.

You could try to use event handlers, but it's EASY to disable event handlers. The point I tried to make earlier was that it's be EASY for perverse users to bypass any event handlers you use to try to prevent copying worksheets.

If you want to allow rearranging worksheets but not copying worksheets within the same workbook, you may still need to protect the workbook's structure but also provide macros which would allow for rearranging worksheets. Such macros would prompt for where the user wants to move ActiveSheet, then unprotect the workbook, move ActiveSheet, and reprotect the workbook.

At some point if you receive no other responses, you may either need to accept that what I've stated above is the case, no matter how much you don't like that and wish it were otherwise, or ask this in a different forum, such as StackOverflow, Reddit's /r/Excel, or MSFT's own community forums in hope that people other than those here might have ideas not forthcoming here.

[Lazarus Rises]

hrIngrv,

Thanks, I see what you mean.

Would you be able to help with a sample macro that would prompt the active sheet to allow moving and renaming?

[Lazarus Rises]

OK So I have a macro that allows you to rename a sheet with prompts.

However I have 4 specific sheets ("Summary", "Material List", "Material Summary", "Template"), that I would like to be exempt from this code with a pop up message that says: "This sheet cannot be renamed"

Also check if the new sheet name is already in use, with a pop up message that says: "Sheet name already in use"

Also still need something that will allow me to unlock the workbook and move sheets with the exception of the 4 specific sheets.

Please Login or Register  to view this content.

[Lazarus Rises]

Sorting my own problems..


The other way I thought of with renaming sheets is to just have the macro available to any sheet, except the 4 specific sheets listed earlier.

Still need a macro that will allow the moving of sheets.

Please Login or Register  to view this content.