Is it possible to protect a sheet so that it can not be selected?

[snoopy80]

Morning,
I have an Excel file that has 2 worksheets. Both of them have command buttons and macros and are protected and users are supposed to update the fields that they should be doing. However, some of them just Select the entire sheet and did a Copy and Paste in to a new one and modify it. My question is Can I block a sheet to be selected and be copied and pasted?
Thanks!

 

[xlbo]

Just about but it would take a LOT of work - you must think about whether it is worth it.


Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

You could have all the good stuff on sheet1, then have the user input data into sheet2. Sheet1 would just have references to sheet2.

Then you can 'veryhide' sheet1, like this:

Sub HideSheet1()
Sheets(1).Visible = xlVeryHidden
End Sub

This will result in Sheet1 being hidden from sight, and if a user goes to Format > Sheet > Unhide to try to unhide any hidden sheets, Sheet1 won't even show as an option! Of course, anyone else can easily defeat this with their own VBA, but to the average user it is as if this sheet doesn't exist.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[combo]

Without additional coding, as Geoff pionts, if you can select, you can also copy and paste.
However you can change 'EnableSelection' sheet's property, to xlUnlockedCells or xlNoSelection, depending on your needs (effect only on protected sheet, can be annoying for the user).

combo

 

[Loomah]

If you really want to go down the coding route it would be possible to disable Copy/Cut/Paste whenever the relevant sheet is activated. You'd then need to re-enable them when the sheet isn't active. However you'd need to think carefully about where code would go, ie Workbook open & beforeclose and window activate & windowdeactivate events, worksheet activate & deactivate etc.

If you really need this that much then there is (if I remember correctly) a FAQ on disabling commands.

So, like xlbo has already mentioned, a bit of work involved!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[macropod]

And easily defeated by disabling the macro when the workbook is opened.

One way of circumventing that might be to have all except one worksheet hidden by default, and only unhidden for use if the protection macro is allowed to run. With the one visible sheet, you could likewise hide all rows & columns by default, only making them unhidden for use if the protection macro is allowed to run.

Cheers