Worksheet_Change Event with Range Protection
 

I have been copying and pasting pieces of codes, when I finally get it to work, I do something and it stops working, recently, I added Range Protection (passwords).

Objective:
I would like for cell B4 to update/timestamp the current date and time when any of the cells within the range "Data_UpdatesUS" changes. I would also like for cell C4 to update if the range "Data_UpdatesEU" changes, and so on for IN, CN and BR.

To understand, IN=India, CN=China, etc. The range of cells represents salary information for each of the employee types in column A.

I have Range Protection (password protected, let's say 1234US) applied to each of the named ranges. I also have worksheet protection (password protected, let's say 1234).

This code works if the sheet is NOT protected at all:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target, Range("Data_UpdatesUS")) Is Nothing) Then
Application.EnableEvents = False
Application.EnableEvents = True
End If
If Not (Application.Intersect(Target, Range("Data_UpdatesEU")) Is Nothing) Then
Application.EnableEvents = False
Range("C4") = Now()
Application.EnableEvents = True
End If
If Not (Application.Intersect(Target, Range("Data_UpdatesIN")) Is Nothing) Then
Application.EnableEvents = False
Range("D4") = Now()
Application.EnableEvents = True
End If
If Not (Application.Intersect(Target, Range("Data_UpdatesCN")) Is Nothing) Then
Application.EnableEvents = False
Range("E4") = Now()
Application.EnableEvents = True
End If
If Not (Application.Intersect(Target, Range("Data_UpdatesBR")) Is Nothing) Then
Application.EnableEvents = False
Range("F4") = Now()
Application.EnableEvents = True
End If
End Sub

However, when I add the protection, it stops working.

Is there anyone who can help???

Hi,

try to do the worksheet protection by VBA-code rather than manually.
Pay attention to Worksheet.protect methods "Userinterface only" property.

UserInterfaceOnly - Optional - Variant - True to protect the user interface, but not macros. If this argument is omitted, protection applies both to macros and to the user interface.

If UserInterfaceOnly = True, then users can't change values at worksheet but your code can. Even if the worksheet is protected.

***

I'm sorry, I forgot to mention that I am oblivious to Visual Basic code. Everything I had done so far is just by copy or trial and error. I am not sure how to incorporate the protection in the code. Will I need to insert the "unprotect" and "protect" before and after each event? Can I still use a password? Will it still work the same way that it does right now?

The worksheet that this is for, contains salary information for each country/office. This information will be linked/referenced in another worksheet within the same workbook. I would like to have two levels of protection, one that allows only managers to access the salary worksheet, the other protection is to prevent managers from editing the timestamp cells. I hope that helps make my intentions clearer.

Thank you so much for your reply. I don't know where else I can find this information. You're my hero.

Hello,

You only have to run this short macro once to protect your active worksheet and workbook.
Your macro ( Private Sub Worksheet_Change(ByVal Target As Range) ) should work even if
worksheet is protected. This is because protection concern only users not VBA-code, therefore
code can change values at C4:F4.

Perhaps you should try following advice with a copy of your original file.
It worked okei, with my worksheet which i made to test your macro. It's fine too.


Try these steps :

1 ) Open the file you are goin to protect

2 ) Press ALT + F11

3) If you dont have any modules at your Project, insert one
by selecting INSERT - MODULE

4 ) paste the following code at empty module window ( you can add
one module even if there already are one or more modules
at your workbook )

Sub ProtectedByPassWord()

ActiveSheet.Protect UserInterfaceOnly:=True, Password:="SH", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Password:=("WB"), Structu=True, Windows:=False

End Sub

5) save the code ( press CTRL + S or save by selecting FILE - SAVE )

6) Switch to normal Excel wiev

7 ) Press ALT + F8 to see macros that you are able to run

8) find ProtectedByPassWord, select it and press run

9) the previous action protected your active sheet with password "SH" and workbook
with password "WB"

10 ) Now you can test if the worksheet and workbook works as before protection

You can unprotect the sheet and workbook normally manually but the passwords
are those used in ProtectedByPassWord - macro.

Ofcourse you can put your own passwords instead of these easy ones. Place them
between the quotation marks, exactly where SH and WB is now.

If you try this macro first with my passwords, you have to manually remove protection
from worksheet and workbook before you can run ProtectedByPassWord - macro
with your own passwords.


*********

ATTENTION :

Be careful with these passwords, anyone who can see your ProtectedByPassWord - macro
can easily unprotect your workbook and worksheet. To prevent that try to run
this macro from another workbook. You may have to refer to the workbook you want to
protect by name then.

*********