Prevent user from making a copy of a sheet.

[GeorgeB]

Good morning all.
Any way to prevent a user from making a copy of a sheet in the same workbook??

 

[Mudface]

Protecting the workbook (Tools- Protection- Protect Workbook, give password if you want), will prevent them from adding any more sheets to the workbook.

Hope this is what you meant

 

[GeorgeB]

Not exactly. I want to prevent copies of specific sheets and allow others.

 

[Mudface]

Hmm, the only way I could think of is to add the following code to each of the worksheets you don't want to be copied. It's not very subtle and I'm sure there's a better way of doing it (you can get round the protection by selecting more than one sheet and copying them)-

Private Sub Worksheet_Activate()

ThisWorkbook.Protect "MyPassword"

End Sub

Private Sub Worksheet_Deactivate()

ThisWorkbook.Unprotect "MyPassword"

End Sub

 

[Jack in the UK]

Hi ==

many different options open all have dangers to be got enrn about ands so still copy.

Only real fix i know is:

If people need access your a bit stuck as all can be got around by advanced user.

All that i have found is what i call real lock down,

Autpen and remove all the toolbar ie that one that carries copy.

Also the right click copy command need disable. and other bits, so not so simple.

Also i tried to stop copy and name other than the original sheets name so will not acheive anything.

All in all not very satisfactry. But BTW can still send to A and play at home????

HTH


Also before i forget what ever is done in Autpen needs to be reversed in Auto_Close else excel might just be that way for lief!

 

[Guest]

Thanks for your input Mudface and Jack.
I haven't tried your solution Jack but Mudface, your's does work. You are correct, however, about end running around it by grouping two sheets and making copys of both. This works as long as the first sheet in the group is not protected. Must be a better way.