Protecting VBA code even if copied into new workbook

[Kenny15]

I have a situation that may actually be a flaw in Excel.  I have a workbook where I have some VBA code.  I have password protected the code. In the same workbook I have code on the individual sheet by right clicking on the tab and select view code where I enter the code.  When I password protect the workbook code by going into tools menu and selecting VBAproject properties clicking on the protection tab and entering a password.  This will also protect the code I entered on the view code tab of the worksheet as well.

The codes stays protected in the workbook.  However if someones copies the sheet we have had the code into a new workbook the password protection is gone and they can now view the code. 

On this same sheet I have password protected certain cells and when they copy the same sheet the password protection stays for the locked cells but not the code when copied into a new workbook.  Lastly the code that I had protected no longer works when also copied into a new workbook.

I have the following code on the this workbook and when they copy the sheet into a new workbook the code no longer works as well as the passwords.  

Private Sub Workbook_Open()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
' Add condition for specifying sheet names if needed
If Date > #1/1/2020# Then
sh.EnableCalculation = False
End If
Next
End Sub

How can I keep the password protection even if someone copies the sheet into a new workbook and also make sure the code still applies when copied into a new workbook.

[#2]

Hi @Kenny15,

The protection tools in Excel are really weak and can be easily hacked.

I have asked the Excel team about this issue before in this link.

With regards to the flaw you've mentioned, the worksheet that has a code should be blocked from copying and moving into another workbook or at least moving or copying without carrying the code.

It's impossible to keep the protection after you copy the worksheet because the protection is on the whole VBA project level, not on the worksheet level.

The solution is to have a VBA protection feature that works on the worksheet level.

And this is what we are missing now!

However, you have an option in the Review tab to protect the workbook to prevent its worksheets from copying or moving in addition to other restrictions.

There is no way at this time to be able to copy or move a protected worksheet into another workbook and keep the worksheet code protected.

If don't want to use the Protect Workbook feature in the Review tab, I have a suggestion for you, it's to refactor the code in the workbook so that you keep the main methods and functions outside the worksheets code module.

You can store them in separate modules or class modules and call them from the worksheets code modules.

Example

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Call test
End Sub

The test is a sub stored in a separate module outside the worksheet1 code module.

Sub test()
 MsgBox "Hello World!"
End Sub

Regards,

Haytham

[#3]

@Haytham Amairah 

Thank, Haytham.  How do you use the review tab to protect the sheet from being copied

[#4]

@Haytham Amairah 

Thanks, Haytham.  How do you use the review tab to protect the sheet from being copied

[#5]

This is can be done using the Protect Workbook under the Protect group in the Review tab.

[#6]

@Haytham Amairah 

ok thanks