Prevent user from making a copy of a sheet.

GeorgeB · Mar 2nd, 2002, 11:37 AM

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

Mudface · Mar 2nd, 2002, 12:40 PM

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 · Mar 2nd, 2002, 03:01 PM

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

Mudface · Mar 2nd, 2002, 03:24 PM

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 · Mar 2nd, 2002, 03:42 PM

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,

Auto_Open 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 Auto_Open needs to be reversed in Auto_Close else excel might just be that way for lief!

Mar 2nd, 2002, 07:34 PM

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.

Mar 2nd, 2002, 03:42 PM	#5
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,065	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, Auto_Open 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 Auto_Open needs to be reversed in Auto_Close else excel might just be that way for lief! __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 2nd, 2002, 11:37 AM	#1
GeorgeB Board Regular Join Date: Feb 2002 Location: Las Vegas Nevada USA Posts: 240	Good morning all. Any way to prevent a user from making a copy of a sheet in the same workbook??

Mar 2nd, 2002, 12:40 PM	#2
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	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

Mar 2nd, 2002, 03:01 PM	#3
GeorgeB Board Regular Join Date: Feb 2002 Location: Las Vegas Nevada USA Posts: 240	Not exactly. I want to prevent copies of specific sheets and allow others.

Mar 2nd, 2002, 03:24 PM	#4
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	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

Mar 2nd, 2002, 07:34 PM	#6
Guest Posts: n/a	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.