Protected Sheet and Locked Cells Compromised!

Hello All,

Some "CLever" guy messed up my Program.

From an UnProtected Sheet he Selected a Range and clicked on Copy. He then Entered "Desing Mode" via the Developer Tab in the Ribbon, and then Activated the Protected Sheet. Then He exited "Design Mode" and Pasted Values via a CommandButton into the Locked Cells of the Protected Sheet, erasing my formulas!!!

The Code for the CommandButton is:

Please Login or Register  to view this content.

Within the Protected Sheet itself it works fine, but,

How can I improve on the code, for this not to happen?

Many Thanks

I can't seem to replicate the approach as it keeps giving me a run-time 1004 error (which is what should happen to prevent pasting ontop of protected cell). Can you upload an example?

Unfortunately, as you have discovered, Excel *IS NOT* a secure platform. There are some good hacking tools a "clever" user could acquire to crack your protection.
However, you might try locking your VBA Project (but *MAKE SURE* you don't loose the password.

The problem with Excel is that users have the option to enable macros or not, If they choose to not enable them your code is defeated.
You could use a separate workbook at a "gateway" to test if macros are enabled and then open the real workbook when the code is enabled.

If they don't enable the code, then they don't get access to the workbook.

You might also consider hiding the developer tab when the workbook is opened.
See the YouTube video from MacroMountain.Com

As far as your code goes, you could, at the onset, test if sheet protection is on or off. If off, display a message and exit the macro, skipping all the code that does the copy/paste.

Here is the copy I used, you can see if maybe I am doing it correctly.

Hi Guys,

As promised, my report back.

The issue is that I want to be able to use my Code to Paste what I have Selected and Copied in one Active Sheet. And that works just fine!. However, as I have explained the issue about some "Clever" Guy in my first Post, it should NOT be possible to Paste from another Sheet using his method since the VBA project is re-activated when he exits Design Mode and the Sheet he is Copying to is Protected. My VBA Project is also PassWord Protected.

I have replicated the problem on the WorkBook aboutsetta gave me. See Attachment.

In my Actual WorkBook I have to Unprotect the actual Sheet in question in order to perform some tasks, and then Protect it again. All via VBA. This seems where the problem starts. It is as if VBA does not Protect the WorkBook the Same way as Protection via the Ribbon does.

Thank you for your time !

Hi Winon,

I still can't reproduce your problem in Excel 2010, but I did notice something interesting which could lead to a solution. When in Design Mode, viewing the other sheets does not trigger the Worksheet_Activate Event. When outside of Design mode, viewing an object (e.g. worksheet) either by manually clicking on the worksheet tab or through vba triggers the event code. Not sure how to disable the Design Mode but might be the culprit.

Hope this helps.

abousetta

If you want to avoid 'clever' users:
Only use userforms for any user interaction, make the workbook invisible (application.visible=false) or make it an addIn, protect the VBAproject with a password.

Originally Posted by Winon

In my Actual WorkBook I have to Unprotect the actual Sheet in question in order to perform some tasks, and then Protect it again. All via VBA. This seems where the problem starts. It is as if VBA does not Protect the WorkBook the Same way as Protection via the Ribbon does.

I have seen this problem before. The thread was kind of old but I remember that protecting the worksheet via vba could be unprotected via vba without a password even though a password was provided during the sheet protection. There may be problems with sheet protection via vba or specific functions that are protected differently between the two methods.

You could restrict the scrollarea(manually or by VBA)
Use an application.inputbox.
Check the result of this inputbox:

Please Login or Register  to view this content.